CODEWITHSUNDEEP
sqlteradatateradata-sql-assistant
WeldaSudha
WeldaSudha

Reputation: 41

Case in Where clause not working properly on Teradata?

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?

Upvotes: 0

Views: 1140

Answers (5)

WeldaSudha
WeldaSudha

Reputation: 41

This is what works me, but I probably confused you guys in the description. Thank you so much for the quick respond tho. I really appreciate your help and time. 

AND (CASE WHEN mp.sls_trans_web_orgn_chnl_name  = 'CA_FACEBOOK'  AND   fsk.full_sku_unit_first_cost  <  500 AND  (juice/NullIfZero(gms))*100  >= 7 THEN 1 
  WHEN mp.sls_trans_web_orgn_chnl_name  <> 'CA_FACEBOOK'  THEN 1 ELSE 0 END ) = 1

Upvotes: 0

Hogan
Hogan

Reputation: 70523

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'
    )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Don't use CASE:

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

The problem is that Teradata does not support returning a boolean type from a CASE expression. Hence, your comparisons in the THEN clauses are not understood syntactically.

EDIT:

Based on your comment, the logic you want is:

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

Upvotes: 0

Fred
Fred

Reputation: 2080

Based on your comments that the original ELSE condition is supposed to be ANDed instead:

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 the prompt value
    AND fsk.item_create_dt >=  '2019-10-20'
    AND  net_pct >= 10
    AND (mp.chnl_name  <> 'CA_FACEBOOK' -- if not Facebook, don't care ORed terms
         OR (GP*100  >= 7 AND fsk.first_cost > 500))
GROUP BY 1,2,3,4,5,6,7,8,9,10,11 /* ,fsk.first_cost */

I don't think you want to GROUP BY first_cost. And this implicitly assumes mp.chnl_name is not NULL, so it's either equal to CA_FACEBOOK or not equal. If NULLs are allowed, you would need to explicitly check that condition also, e.g. (mp.chnl_name <> 'CA_FACEBOOK' OR mp.chnl_name IS NULL).

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

This might be what you want:

WHERE 
  (mp.chnl_name <> 'CA_FACEBOOK' 
    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
OR (( mp.chnl_name  = 'CA_FACEBOOK' AND GP*100  >= 7 AND fsk.first_cost > 500))

But if you're creating this WHERE-condition in your application (the 1=1 seems to indicate this) you better apply your logic there.

Upvotes: 0

Related Questions