Answered by:
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
ThanksFriday, 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%' )
- Edited by Kent Waldrop Friday, November 16, 2012 8:24 PM
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