Case statement in Where Clause

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

Answer 1

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

Answer 2

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%'
      )

Answer 3

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)

Answer 4

Can you post the complete query pls ?

---

Sergio Sánchez Arias

Answer 5

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
*/

Answer 6

You know what, I was confused.  But now I see what I was missing.

You were right. Thanks a lot for the help.