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
