SQL use CASE statement in WHERE IN clause

Question

Is it posible to use case in where in clause  Something like this    DECLARE  Status VARCHAR 50    SET  Status  published     SELECT   FROM Product P      WHERE P Status IN  CASE WHEN  Status  published  THEN  1 3                                     WHEN  Status  standby  THEN  2 5 9 6                                     WHEN  Status  deleted  THEN  4 5 8 10                                     ELSE  1 3                                     END    This code gives the error   Incorrect syntax near

User · Answer

SELECT    FROM Tran LibraryBooksTrans LBT    LEFT JOIN Tran LibraryIssuedBooks LIB ON   CASE WHEN LBT IssuedTo  SN  AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1       WHEN LBT IssuedTo  SM  AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1        WHEN LBT IssuedTo  BO  AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1  ELSE 0 END

User · Answer

I believe you can use a case statement in a where clause  here is how I do it   Select  ProductID OrderNo  OrderType  OrderLineNo From Order Detail Where ProductID in   Select Case when   Varibale1         then  Select ProductID from Product P Where          Else  Select ProductID from Product  End as ProductID     This method has worked for me time and again  try it

User · Answer

select    from Tran LibraryBooksTrans LBT  left join  Tran LibraryIssuedBooks LIB ON   case WHEN LBT IssuedTo  SN  AND  LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1 when LBT IssuedTo  SM   AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1 WHEN  LBT IssuedTo  BO  AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1  ELSE 0 END enter code here select    from Tran LibraryBooksTrans LBT   left join Tran LibraryIssuedBooks LIB ON   case WHEN LBT IssuedTo  SN   AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1 when  LBT IssuedTo  SM  AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN 1  WHEN LBT IssuedTo  BO  AND LBT LIBRARYTRANSID LIB LIBRARYTRANSID THEN  1 ELSE 0 END

User · Answer

According to my Scenario  I did  quot use CASE statement in WHERE IN clause quot  like following  AdjType varchar 20     Value     Base varchar 20     Common  where   select CASE WHEN SA IsPersentage   0 THEN  Value          WHEN SA IsPersentage   1 THEN  Presentage  END  Like  AdjType and  Select CASE WHEN SA IsDependOnBasicSalary   0 THEN  Common           WHEN SA IsDependOnBasicSalary   1 THEN  Basic Salary  END  like  Base

User · Answer

No you can t use case and in like this  But you can do  SELECT   FROM Product P     WHERE  Status  published  and P Status IN  1 3  or  Status  standby  and P Status IN   2 5 9 6  or  Status  deleted  and P Status IN  4 5 8 10  or P Status IN  1 3    BTW you can reduce that to   SELECT   FROM Product P     WHERE  Status  standby  and P Status IN  2 5 9 6  or  Status  deleted  and P Status IN  4 5 8 10  or P Status IN  1 3    since or P Status IN  1 3  gives you also all records of  Status  published  and P Status IN  1 3

User · Answer

I realize this has been answered  but there is a slight issue with the accepted solution   It will return false positives   Easy to fix   SELECT   FROM Products P     WHERE   Status  published  and P Status IN  1 3      or   Status  standby  and P Status IN   2 5 9 6      or   Status  deleted  and P Status IN  4 5 8 10      or   Status not in   published   standby   deleted   and P Status IN  1 2      SQL Fiddle Demo   Parentheses aren t needed  although perhaps easier to read hence why I included them

User · Answer

maybe you can try this way       SELECT         FROM Product P       WHERE  CASE          WHEN  Status    published  THEN            CASE             WHEN P Status IN  1  3  THEN               TRUE              ELSE              FALSE           END           WHEN  Status    standby  THEN            CASE             WHEN P Status IN  2  5  9  6  THEN               TRUE              ELSE               FALSE            END           WHEN  Status    deleted  THEN            CASE             WHEN P Status IN  4  5  8  10  THEN               TRUE              ELSE               FALSE            END           ELSE            CASE             WHEN P Status IN  1  3  THEN               TRUE              ELSE               FALSE            END         END     TRUE    In this way if  Status    published   the query will check if P Status is among 1 or 3  it will return TRUE else  FALSE   This will be matched with TRUE at the end  Hope it helps

[sql] SQL use CASE statement in WHERE IN clause

Examples related to sql

Examples related to select

Examples related to switch-statement

Examples related to case

Examples related to where-in