Menu
My first time working with CASE Logic in SQL statements. Everything works if I remove the CASE statements, so the SQL is valid without it.
I need to calculate the total item price based on a couple of things.
If "Sales Price" is active AND "Option Upcharge" has a value, the total is: Qty * (Sales Price + Option Upcharge)
If "Sales Price is inactive AND "Option Upcharge" has a value, the total is: Qty * (Price + Option Upcharge)
If "Sales Price" is active AND "Option Upcharge" has NO value, the total is: Qty * Sales Price
If "Sales Price is inactive AND "Option Upcharge" has NO value, the total is: Qty * Price
If no Option was added, the value for tblproduct_options.option_upcharge is NULL in the output.
Thanks for the help.
Brett
Here is my SQL:
SELECT tblshopping_cart.session_id, tblshopping_cart.product_id, tblshopping_cart.product_qty, tblshopping_cart.product_option, tblproducts.product_title, tblproducts.product_price, tblproducts.product_sale_price_status, tblproducts.product_sale_price, tblproduct_options.option_text, tblproduct_options.option_upcharge, CASE WHEN (tblproducts.product_sale_price_status = 'Y') CASE WHEN (tblproduct_options.option_upcharge IS NOT NULL) THEN (tblshopping_cart.product_qty * (tblproducts.product_sale_price + tblproduct_options.option_upcharge)) ELSE (tblshopping_cart.product_qty * tblproducts.product_sale_price) END ELSE CASE WHEN (tblproduct_options.option_upchage IS NOT NULL) THEN (tblshopping_cart.product_qty * (tblproducts.product_price + tblproduct_options.option_upcharge)) ELSE (tblshopping_cart.product_qty * tblproducts.product_price) END END AS product_total FROM tblshopping_cart INNER JOIN tblproducts ON tblshopping_cart.product_id = tblproducts.product_id LEFT JOIN tblproduct_options ON tblshopping_cart.product_option = tblproduct_options.option_product_id ORDER BY tblshopping_cart.product_qty ASC It fails with with message:
CASE WHEN (tblproduct_options.option_upcharge IS NOT NULL) THEN (tblshopping_' at line 4 
555
In the Nested CASE expression, the outer statement executes until satisfying a condition. Once the condition is met, the inner CASE expression executes and returns a single result. If no condition is met in the outer statement, CASE expression returns the value in the ELSE statement.
Definition and Usage. The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause.
The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well. In this article, we would explore the CASE statement and its various use cases.
You are missing a THEN in your first CASE Statement. (sorry I had to add table aliases)
SELECT sc.session_id , sc.product_id , sc.product_qty , sc.product_option , p.product_title , p.product_price , p.product_sale_price_status , p.product_sale_price , po.option_text , po.option_upcharge , CASE WHEN (p.product_sale_price_status = 'Y') THEN <-- add this CASE WHEN (po.option_upcharge IS NOT NULL) THEN (sc.product_qty * (p.product_sale_price + po.option_upcharge)) ELSE (sc.product_qty * p.product_sale_price) END ELSE CASE WHEN (po.option_upchage IS NOT NULL) THEN (sc.product_qty * (p.product_price + po.option_upcharge)) ELSE (sc.product_qty * p.product_price) END END AS product_total FROM tblshopping_cart sc INNER JOIN tblproducts p ON sc.product_id = p.product_id LEFT JOIN tblproduct_options po ON sc.product_option = po.option_product_id ORDER BY sc.product_qty ASC If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
