Case in Where clause not working properly on Teradata?

Question

Can anybody please tell me what is wrong with my query. I know for sure it is something wrong with my case statement inside where clause for sure. Without the Case statement, this query will run fine.

Error – SELECT Failed. 3707:Syntax error, expected something like an ‘END keyword between ‘)’ and “>=”

SELECT 
    mp.chnl_name AS mp_channel,
    fsk.sku_prod_id AS pro_id,
    fsk.dateint  AS avlbl_dt,
    fsk.sku_num AS sku,
    fsk.upc_txt AS UPC,
    Coalesce(fsk.brand_name,'N/A') AS brand,
    fsk.ruckload AS LTL_Flag,
    fsk.price_amt AS item_selling_price,
    fsk.on_hand_unit_qty AS qoh,
    fsk.netpropt AS profite,
    (fsk.price_amt  + fsk.shpg_amt) AS lms,
    (GP)*100 AS net_pct
FROM EDW.ITEM_AVLBL mp
JOIN EDW.FULL_SKU fsk ON mp.item_id = fsk.item_id
JOIN  EDW.SHORT_SKU ssk ON ssk.sh_sku_id = fsk.sh_sku_id
WHERE 1=1
    AND mp.chnl_name  LIKE '%google%'  ---------- This is a prompt but I hard coded to test
    AND fsk.item_create_dt >=  '2019-10-20'
    AND  net_pct >= 10
    AND CASE WHEN mp.chnl_name  = 'CA_FACEBOOK'  THEN  ((GP)*100)  >= 7 ELSE  fsk.first_cost > 500 END
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,fsk.first_cost

Can anybody please tell me how to fix this?

Answer 1

Case does not work in where here, you can re-write to use ANDs and ORs

AND ( (mp.chnl_name  = 'CA_FACEBOOK' AND ((GP)*100)  >= 7 ) 
    OR (mp.chnl_name  != 'CA_FACEBOOK' AND fsk.first_cost > 500) ) 

---

according to what you said below add the following to the where:

AND ( (mp.chnl_name  = 'CA_FACEBOOK' AND ((GP)*100)  >= 7 AND fsk.first_cost > 500)
      OR mp.chnl_name  != 'CA_FACEBOOK'
    )