SQL Sever: in...case...in WHERE clause

Question

Hi, I need to code up a query for something like this:

Select [something]
Where
condition in
case
when (if another_condition = A and 3rd Condition = B) then (C,D)
when (if another_condition = N and 3rd Condition = E) then (F,G)
else (J,K)
end

essentially, what I want is if A and B are met, condition could be set to either C or D, if N or E are met, then condition could be set to F or G, else condition set to J or K. However, when I run this, I kept getting Incorrect syntax near the keyword 'Case'.

Please help! Thanks!

Answer 1

How about this - the UNION subquery will give you the full result set within the subquery. Then you can say 'WHERE condition IN ' (subquery). Like this:

SELECT [something]
WHERE
condition IN
(SELECT CASE WHEN (another_condition = A AND 3rd Condition = B) THEN C
   WHEN (another_condition = N AND 3rd Condition = E) THEN F
   ELSE J
   END AS Value

UNION

SELECT CASE WHEN (another_condition = A AND 3rd Condition = B) THEN D
   WHEN (another_condition = N AND 3rd Condition = E) THEN G
   ELSE K
   END AS Value
)

Answer 2

Maybe this:

Where  (Another_Condition = 'A' And Third_Condition = 'B' And Condition in ('C','D'))
       Or 
       (Another_Condition = 'N' and Third_Condition = 'E' And Condition in ('F','G'))
       Or 
       Condition In ('J','K')

Be very careful about mixing and's and or's in a where clause. Parenthesis are important.

Answer 3

I'd probably go with G Mastro's approach of expanding the query as a Boolean expression. While the nested query approach will work, the intent of the code is less obvious IMO.

Having said that, if there are a lot of cases in your CASE statement, you may want to consider reshaping your data, because no matter how you write the query, it boils down to a big Boolean expression.