none
Using IN within a CASE

    Question

  • 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 

    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.  

    Monday, March 04, 2013 7:58 PM

Answers

  • 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 8:42 PM

All replies

  • 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:06 PM
  • 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 8:42 PM

  • >> 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

    Monday, March 04, 2013 9:39 PM