Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Using IN within a CASE

Answered Using IN within a CASE

  • Monday, March 04, 2013 7:58 PM
     
     
    Sign In to Vote
    0

    I would like to use CASE and return a list.   Here is my query:

    DECLARE @RX varchar(1)
    SET @RX = '1'

    SELECT
            U.time, 
    U.Node
    From 
    U 
    Where 
            U.time between '2/11/13' and '2/11/13' and   
    U.node in (
        case @RX 
    when '1' then ('ABC')
    when '2' then ('ABC','DEF')
    END)

    The CASE when @RX=2 doesn't work.  

     

All Replies

  • Monday, March 04, 2013 8:06 PM
     
      Has Code
    Sign In to Vote
    0

    You need to change your query for this:

    DECLARE @RX varchar(1)
SET @RX = '1'

SELECT
        U.time, 
U.Node
From 
U 
Where 
        U.time between '2/11/13' and '2/11/13' 
and   
(
 (@RX = '1' and u.node IN ('ABC'))
 OR
 (@RX = '2' and u.node IN ('ABC','DEF')) 
)

    Sergio Sánchez Arias
    AYÚDANOS A AYUDARTE

     
  • Monday, March 04, 2013 8:42 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    There are several problems with the above. First of all, why do you declare 1 character variable as varchar(1) instead of char(1)? Secondly, I suggest to use two separate queries for your two cases and don't put @Rx variable into the query at all.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Monday, March 04, 2013 9:39 PM
     
     
    Sign In to Vote
    0

    >> I would like to use CASE and return a list. <<

    CASE is an expression; expression return scalar values. This is not just SQL, but any programming language. TIME is a reserved word in SQL and the rest of your code violates ISO-11179 and other industry standards. Please consider how silly VARCHAR(1) is. Why don't you know the ISO-8601 date format? 

    If you are really determined to use CASE, try this: 

    DECLARE @local_something_flg CHAR(1);

    SELECT U.screwup_date, Something_node
      FROM Unnamed_Stuff AS U
     WHERE U.screwup_date BETWEEN '2013-02-11' AND '2013-02-11' 
       AND something_node 
           IN (CASE WHEN @local_something_flg IN ('1', '2')
               THEN 'ABC' ELSE '' END, 
           CASE WHEN @local_something_flg ='2')
                THEN 'DEF' ELSE '' END);

    I am assuming that something_node cannot be an empty string. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL