What should I do in an sql case when multiple conditions?

593    Asked by BenjaminMoore in SQL Server , Asked on Sep 27, 2022

 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.


Answered by Ben PHILLIPS

In an sql case when multiple conditions-


,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



Your Answer

Interviews

Parent Categories