Case Statement in 'IN' clause in SQL

Question

I am trying to write a case statement inside the IN clause.

The Channel column contains three values ACT, REN, REP. I want to select more than 1 values, but I get result with only one value.

    select * from tbl
    WHERE tbl.Channel IN (select 
                                    case when @ACT =1 then 'ACT'
                                    when @REN =1 then 'REN' 
                                    when @REP =1 then 'REP' END)

What changes should I be doing here ?

Possible duplicate: http://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not — Kris Gruttemeyer, Nov 04 '14 at 21:40
Are you using MySQL or SQL Server? Please only tag with the platform you are actually using. Those tags have descriptions when you mouse over them; please read them before using them. — Aaron Bertrand, Nov 04 '14 at 21:54
@AaronBertrand I see that a lot recently. Probably due to this http://meta.stackoverflow.com/questions/275170/stop-generic-database-questions-from-getting-mysql-as-a-suggested-tag — Martin Smith, Nov 04 '14 at 22:04
@Martin Could be. Also see http://meta.stackexchange.com/questions/207274/suggested-tags-both-sql-server-and-mysql-are-suggested-by-the-system and the discussion under Shog's answer [here](http://meta.stackoverflow.com/questions/274632/help-the-helpless-with-how-to-ask-tag-tips). — Aaron Bertrand, Nov 04 '14 at 22:09
@AaronBertrand hopefully they extend the new tag tips feature to be a bit more intelligent and recognise specific RDBMS platforms so it doesn't warn you when it is already tagged. Then I guess it will be a short step to recognise you have multiple tagged. — Martin Smith, Nov 04 '14 at 22:12
@Martin Yeah, I do sense that it is getting worse and not better. — Aaron Bertrand, Nov 04 '14 at 22:18

score 4 · Answer 1 · answered Nov 04 '14 at 21:39

4

Don't end your case. You are returning 3 columns. You only want one.

select * from tbl
    WHERE tbl.Channel =  case when @ACT =1 then 'ACT' 
                              when @REN =1 then 'REN'
                              when @REP =1 then 'REP' 
                              else NULL END

answered Nov 04 '14 at 21:39

Bill Gregg

7,067
2
22
39

Then ACT would be returned first. – Bill Gregg Nov 04 '14 at 21:44
I want to select more than one option out of REn, REP and ACT. Here, I get only 1 value – user1989 Nov 04 '14 at 21:49

Martin Smith · Accepted Answer · 2014-11-04T21:59:26.747

This will work fine. SQL Fiddle

The only change is removing the select so the input to the in clause is 3 values rather than a single row with three columns.

The implicit else null if the variable is not 1 does not cause a problem in an in clause.

SELECT * 
FROM   tbl 
WHERE  tbl.channel IN ( CASE 
                          WHEN @ACT = 1 THEN 'ACT' 
                        END, CASE 
                          WHEN @REN = 1 THEN 'REN' 
                        END, CASE 
                               WHEN @REP = 1 THEN 'REP' 
                             END )

Gordon Linoff · Answer 3 · 2014-11-04T22:56:45.287

1

You can write the clause without a case at all:

select *
from tbl
where (tbl.Channel 'ACT' and @ACT = 1) or
      (tbl.Channel = 'REN' and @REN = 1) or
      (tble.Channel = 'REP' and @REP = 1);

Your select statement is not correct and should return an error. select statements in in clauses are not allowed to return more than one column.

edited Nov 04 '14 at 22:56

answered Nov 04 '14 at 21:44

Gordon Linoff

1,242,037
58
646
786

When combining AND and OR you really should use parentheses to make the desired logic tree clear. – Aaron Bertrand Nov 04 '14 at 21:53

Sergey Kalinichenko · Answer 4 · 2014-11-04T21:56:59.753

Here is an alternative that should let you perform the selection without an IN operator:

select * from tbl
WHERE (tbl.Channel='ACT' AND @ACT=1)
   OR (tbl.Channel='REN' AND @REN=1)
   OR (tbl.Channel='REP' AND @REP=-1)

If you must use IN operator, you should be able to construct a query with UNION ALL, like this:

select * from tbl
WHERE tbl.Channel IN (
    select case when @ACT =1 then 'ACT' END FROM DUAL
        UNION ALL
    select case when @REN =1 then 'REN' END FROM DUAL
        UNION ALL
    select case when @REP =1 then 'REP' END FROM DUAL)

Demo.

Case Statement in 'IN' clause in SQL

4 Answers4