CASE WHEN statement in an IN statement

Question

I am stuck in a MS SQL SERVER 2012 query. I want to write several values ​​in the "CASE" statement in the "IN" statement of the WHERE clause, see the following:

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

The problem here is 2421, 2431 - they cannot be separated by commas. is there any solution to write this in another way? thanks.

Answer 1

This is simpler if you are not using case in the where clause. Something like that:

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

Answer 2

I 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)) 

Answer 3

You can break it into combinations of AND and OR.

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