locked
Case statement in Where Clause RRS feed

  • Question

  • A description field in the database I'm working in is a ~ delimited field.  When the value of 'Use Corporate Email' exists, I want to only retrieve rows where the customer's region  number is 16 or 17. So I'm trying something like this, but it's not correct. Could you help?

    Where
    some condition here
    AND 
    CASE
        When Description Like '%Use Corporate Email%'
        Then Region_Number in (16,17)
    End

    Thanks
    Friday, November 16, 2012 8:16 PM

Answers

  • Hi rk1975, i never use the CASE statement in WHERE clause, a similar o a common statement could be like this:

    Where
    some condition here
    OR
    ( Description like '%Use Corporate Email%' and Region_Number in (16,17 ) )

    Regards ! ! !


    Sergio Sánchez Arias


    • Edited by Sergio S Arias Friday, November 16, 2012 8:27 PM
    • Proposed as answer by Naomi N Friday, November 16, 2012 8:32 PM
    • Marked as answer by R_i-c_h Friday, November 16, 2012 9:07 PM
    Friday, November 16, 2012 8:21 PM

All replies

  • Hi rk1975, i never use the CASE statement in WHERE clause, a similar o a common statement could be like this:

    Where
    some condition here
    OR
    ( Description like '%Use Corporate Email%' and Region_Number in (16,17 ) )

    Regards ! ! !


    Sergio Sánchez Arias


    • Edited by Sergio S Arias Friday, November 16, 2012 8:27 PM
    • Proposed as answer by Naomi N Friday, November 16, 2012 8:32 PM
    • Marked as answer by R_i-c_h Friday, November 16, 2012 9:07 PM
    Friday, November 16, 2012 8:21 PM
  • Perhaps something like:

    Where region_Number between 16 and 17
      and description like '%Use Corporate Email%'


    or maybe:

    Where ( region_Number between 16 and 17 or
            description not like '%Use Corporate Email%'
          )

    Friday, November 16, 2012 8:23 PM
  • When region_number is 16 or 17 then description has to have 'Use Corporate Email' in it

    But if region_number is anything else description doesn't have to have to that phrase for the row to be returned

    That's why i thought i would need something like

    If description  like '%Use Corporate Email%'  Then

            region_number in (16,17)

    • Edited by R_i-c_h Friday, November 16, 2012 8:28 PM
    Friday, November 16, 2012 8:26 PM
  • Can you post the complete query pls ?

    Sergio Sánchez Arias

    Friday, November 16, 2012 8:30 PM
  • Perhaps something like this:

    declare @test table( region_Number int, description varchar(30));
    insert into @test
    select 14, 'Just use corporate Email, ok?' union all
    select 13, 'Nothing' union all
    select 16, 'Nothing' union all
    select 16, null union all
    select 17, 'Yes, use corporate Email' union all
    select 18, null
    ;
    select * from @test
    where ( region_Number between 16 and 17  
            and description like '%Use Corporate Email%'
            or coalesce(description, 'x') not like '%Use Corporate Email%'
    		and region_number not between 16 and 17
          );
    /* -------- Output: --------
    region_Number description
    ------------- ------------------------------
    13            Nothing
    17            Yes, use corporate Email
    18            NULL
    */

    Friday, November 16, 2012 8:49 PM
  • You know what, I was confused.  But now I see what I was missing.

    You were right. Thanks a lot for the help.

    Friday, November 16, 2012 9:07 PM