CODEWITHSUNDEEP
sqlsql-servert-sqlcasewhere-clause
user2343837
user2343837

Reputation: 1015

CASE in WHERE clause using IN command

I need to execute my statement something like this:

Select myColumn
From myTable
WHERE
CASE 
WHEN Datepart(y,@year_end) = 2010 and Datepart(m,@year_end) = 3 then CalendarQuarter in (@Q1,@Q2,@Q3,@Q4) 
WHEN Datepart(y,@year_end) = 2010 and Datepart(m,@year_end) = 6 then CalendarQuarter in (@Q2,@Q3,@Q4)
WHEN Datepart(y,@year_end) = 2010 and Datepart(m,@year_end) = 9 then CalendarQuarter in (@Q3,@Q4)  
END

I cant get the syntax right. Basically, when I provide year to be 2010 and month 3, I want the first IN statement to be used. If I say month = 6, then I want to second IN statement used.

Upvotes: 0

Views: 148

Answers (1)

ruakh
ruakh

Reputation: 183251

Boolean expressions are distinguished syntactically in SQL; they're not just otherwise-normal expressions that evaluate to a Boolean. So you can't use THEN ... IN ... as a THEN clause in a CASE expression (since that clause expects a normal expression, not a Boolean expression), and you can't use WHERE CASE ... as a WHERE clause (since that clause expects a Boolean expression, which CASE ... is not).

There are various ways to work around this. In your case, you might write something like this:

Select myColumn
From myTable
WHERE Datepart(yy,@year_end) = 2010
  AND CalendarQuarter IN
      ( (CASE WHEN Datepart(m,@year_end) = 3 THEN @Q1 END),
        (CASE WHEN Datepart(m,@year_end) IN (3,6) THEN @Q2 END),
        @Q3, @Q4
      )
;

Upvotes: 1

Related Questions