title: Nested CASE statements in MySQL
body: <p>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.</p>
  
  <p>I need to calculate the total item price based on a couple of things.</p>
  
  <p>If "Sales Price" is <strong>active</strong> AND "Option Upcharge" has a value, the total is: Qty * (Sales Price + Option Upcharge)</p>
  
  <p>If "Sales Price is <strong>inactive</strong> AND "Option Upcharge" has a value, the total is: Qty * (Price + Option Upcharge)</p>
  
  <p>If "Sales Price" is <strong>active</strong> AND "Option Upcharge" has NO value, the total is: Qty * Sales Price</p>
  
  <p>If "Sales Price is <strong>inactive</strong> AND "Option Upcharge" has NO value, the total is: Qty * Price</p>
  
  <p>If no Option was added, the value for <strong>tblproduct_options.option_upcharge</strong> is NULL in the output.</p>
  
  <p>Thanks for the help.</p>
  
  <p>Brett</p>
  
  <p><strong>Here is my SQL:</strong> </p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>It fails with with message:</strong></p>
  
  <pre><code>CASE
      WHEN (tblproduct_options.option_upcharge IS NOT NULL)
          THEN (tblshopping_' at line 4
  </code></pre>