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

Answer 1

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
*/

 

 

Answer 2

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.