Case with multiple conditions
I need to change returned value, from select statement, based on several conditions. I tried something like that:
,CASE i.DocValue WHEN 'F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal ELSE 0 END as Value
why it is not working and what is recommended way to do this ? There will be next several WHEN conditions.
Thank you
,CASE WHEN i.DocValue ='F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal ELSE 0 END as Value
There are two types of
CASE
statement, SIMPLE and SEARCHED.You cannot evaluate multiple expressions in a Simple case expression, which is what you were attempting to do.
-- Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END -- Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END
Example of Simple CASE:
CASE x WHEN 'a' THEN 'b' WHEN 'c' THEN 'd' ELSE 'z' END
Example of a Searched CASE:
CASE WHEN x = 1 AND y = 2 THEN 'a' WHEN x = 2 AND y = 1 THEN 'b' ELSE 'z' END
Further Reading: http://msdn.microsoft.com/en-us/library/ms181765.aspx
You can write like this one too if you have multiple requirements:
CASE WHEN CITY='HYDERABAD' THEN CASE WHEN AREA='BHILL' THEN PIN='12345' WHEN AREA='DSNR' THEN PIN='67890' ELSE '33333' END END AS PIN_COD
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Muflix one year ago
you are right, nested case for complex condition works too :)