How to Nest a Case Statement in a Where Clause and Return Multiple Values
A Representation of the Query I'm Working with Is as Follows. Select T1. Col3 from Table1 T1 Where T1. Col1 in ('Value1','Value2') and (Case When T1. Col1 =...
A representation of the query I'm working with is as follows.
SELECT T1.Col3
FROM Table1 T1
WHERE T1.Col1 IN ('Value1','Value2')
AND (CASE
WHEN T1.Col1 = 'Value1'
THEN T1.Col2 IN ('Value3','Value4','Value5')
WHEN T1.Col1 = 'Value2'
THEN T1.Col2 IN ('Value6','Value7','Value8')
END
)
I always get an Error with the syntax around the second when for some reason. I also get a syntax error at the first comma in both IN statements but not the second. Not that it should make a make a difference, I also tried changing the nested IN statement to an OR statement like the following:
THEN (T1.COL2 = 'Value3') OR (T1.COL2 = 'Value4') OR (T1.COL2 = 'Value5')
Instead I get a syntax error around the first OR statement.
I've used CASE in a WHERE clause before but it only returned one value. The fact that I'm getting a syntax error at either the first comma or OR statement makes me suspect returning multiple values isn't possible.
Any light you could shed on this problem will be greatly appreciated.
P.S. I do already have a work around in place by creating a temp table and running conditional DELETE statements on it, that achieves the same outcome I'm trying to do with the CASE statement above. I am curious however, to know if the other method which this question concerns is possible.
2 Answers
You can simplify the WHERE criteria like below:
SELECT T1.Col3
FROM Table1 T1
WHERE
(
(T1.Col1 = 'Value1' AND T1.Col2 IN ('Value3','Value4','Value5'))
OR
(T1.Col1 = 'Value2' AND T1.Col2 IN ('Value6','Value7','Value8'))
)
The same query could be written as 2 separate queries with a UNION:
SELECT T1.Col3
FROM Table1 T1
WHERE T1.Col1 = 'Value1'
AND T1.Col2 IN ('Value3','Value4','Value5')
UNION -- note that this will return distinct results
SELECT T1.Col3
FROM Table1 T1
WHERE T1.Col1 = 'Value2'
AND T1.Col2 IN ('Value6','Value7','Value8')
Note however, that a WHERE with OR clauses may result in an expensive query plan. If the query is very expensive, a covering index for this query might help:
CREATE INDEX IX_Table1_Col1_Col2_Incl ON Table1 (Col1, Col2) INCLUDE (Col3)
Select col3
From tab
Where col1 = 'val1' and col2 in ('val3', 'val4') or
col1 = 'val2' and col2 in ('val5', 'val6')
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
