# Using Case statement in a Where clause

• ### Question

• Hi,

I would like to know how I will us a case statement in a Where condition. Basically I wanted to use a case statement in a WHERE clause using IN in the condition. The scenario for my query is as below

```Select Column1,
Column2,
Column3,
.
.
.
ColumnN
FROM
TABLE1
WHERE Column2 IN (CASE WHEN Condition1 and Condition2
THEN (2,3)
ELSE 5
END
)
AND COLUMN3 = 1			```

I am getting the error near THEN (2,3) of the where clause. Please let me know how to achieve the secnario like the above

Wednesday, October 12, 2011 5:23 PM

• Select Column1, Column2, ...

From Table1

Where 1 = (Case When Condition1 And Condition2 Then (Case When Column2 In (2, 3) Then 1 When Column2 = 5 Then 1 Else 0 End) End) And Column3 = 1

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
Wednesday, October 12, 2011 5:27 PM

### All replies

• You cannot divide an IN clause like this.  For example:

```declare @test table
( column1 integer, column2 integer, column3 integer)
insert into @test
select 1, 2, 1 union all
select 2, 3, 2 union all
select 3, 3, 1 union all
select 4, 2, 2 union all
select 5, 2, 5 union all
select 6, 5, 1
select column1, column2, column3
--  , column1%3, column1%2
from @test
where column3 = 1
and case when column1%3=1 -- condition 1
and column1%2=0 -- condition 2
and column2 in (2,3) then 1
when ( column1%3<>1 or column1%2=1 )
and column2 = 5 then 1
end = 1
/* -------- Output --------
column1     column2     column3
----------- ----------- -----------
6           5           1
*/

```

• Edited by Wednesday, October 12, 2011 5:36 PM
Wednesday, October 12, 2011 5:26 PM
• Select Column1, Column2, ...

From Table1

Where 1 = (Case When Condition1 And Condition2 Then (Case When Column2 In (2, 3) Then 1 When Column2 = 5 Then 1 Else 0 End) End) And Column3 = 1

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
Wednesday, October 12, 2011 5:27 PM