11

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

21
,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

1

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
  • you are right, nested case for complex condition works too :) – Muflix Aug 10 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.