CODEWITHSUNDEEP
sqlsql-servert-sql
Tedo G.
Tedo G.

Reputation: 1565

"CASE WHEN" operator in "IN" statement

I've stuck in an MS SQL SERVER 2012 Query. What i want, is to write multiple values in "CASE" operator in "IN" statement of WHERE clause, see the following:

WHERE [CLIENT] IN (CASE WHEN T.[IS_PHYSICAL] THEN 2421, 2431 ELSE 2422, 2432 END)

The problem here is in 2421, 2431 - they cannot be separated with comma. is there any solution to write this in other way? thanks.

Upvotes: 5

Views: 1967

Answers (3)

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

You could break it up into a combination of AND and OR.

WHERE ((T.[IS_PHYSICAL]=1 AND [CLIENT] IN (2421, 2431)) 
       OR (T.[IS_PHYSICAL]=0 AND [CLIENT] IN (2422, 2432)))

Upvotes: 2

jarlh
jarlh

Reputation: 44766

I'd use AND / OR instead of a case expression.

WHERE (T.[IS_PHYSICAL] AND [CLIENT] IN (2421, 2431))
   OR (NOT T.[IS_PHYSICAL] AND [CLIENT] IN (2422, 2432))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is simpler if you don't use case in the where clause. Something like this:

where (T.[IS_PHYSICAL] = 1 and [client] in (2421, 2431)) or
      (T.[IS_PHYSICAL] = 0 and [client] in (2422, 2432))

Upvotes: 5

Related Questions