Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

"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.

like image 876
Tedo G. Avatar asked Mar 21 '16 13:03

Tedo G.


People also ask

How do you use an operator in a CASE statement?

using the equality operator (=). If you want to use other comparison operators such as greater than (>), less than (<), etc., you use the searched CASE expression. The CASE statement returns the result_1, result_2, or result_3 if the expression matches the corresponding expression in the WHEN clause.

Can we use case when in WHERE clause?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Can we use and operator in CASE statement in SQL?

A quick review of CASE basics:You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN . This includes stringing together multiple conditional statements using AND and OR .


1 Answers

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))
like image 55
Gordon Linoff Avatar answered Oct 12 '22 23:10

Gordon Linoff