Using CASE in WHERE clause

Question

Post reply

Using CASE in WHERE clause

TroyG

SSCrazy

Points: 2633
More actions
May 15, 2007 at 4:12 pm

#173698
Hi there,
I am trying to use CASE in my WHERE clause so that I can return a variable where clause but i keep getting a message saying "an expression of non-boolean type specified in a context where a condition is expected"
I have searched and it appears as though i am doing something slightly different to the norm:
```
declare @chargeCust varchar(50)
```
```
set @chargeCust = 'test001'
```
```
 
```
```
select top 5 
```
```
dj.job_no
```
```
, dj.cust_cd
```
```
, dj.job_date_time
```
```
, dj.dlv_reqd_datetime_last
```
```
from daily_job dj
```
```
where 
```
```
dj.job_date_time >getdate-7
```
```
and (case 
```
```
when len(@chargeCust)>0 then 'dj.cust_cd in (@chargeCust)' 
```
```
else 'dj.cust_cd is not null' 
```
```
end)
```
So basically what I am trying to do - is do a check so that when the variable @chargeCust is specified with a value - this is used to return the values that match. Whereas if it is not specified, just return everything where the dj.cust_cd field is not null.
Any ideas?
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Vicente Horna Quintana Newbie Points: 7 More actions · Answer 1

Try this code

declare @chargeCust varchar(50)

set @chargeCust = 'test001'

select top 5

dj.job_no

, dj.cust_cd

, dj.job_date_time

, dj.dlv_reqd_datetime_last

from daily_job dj

where

dj.job_date_time >getdate-7 and

((len(@chargeCust) > 0 and dj.cust_cd in (@chargeCust)) or (len(@chargeCust) = 0 and dj.cust_cd is not null))

Tim Mitchell SSCoach Points: 15681 More actions · Answer 2

When you enclose your entire case statement in the parenthesis after your AND clause, the query processor expects a true/false to be returned from within the parenthesis.

You are also not using the varchar variable you declared to filter your query, which I presume you intended to use as part of your WHERE clause?

Try something like this instead:

declare @chargeCust varchar(50)

set @chargeCust = 'test001'

select top 5 dj.job_no
, dj.cust_cd
, dj.job_date_time
, dj.dlv_reqd_datetime_last
, case 
when len(@chargeCust)>0 then 'dj.cust_cd in (@chargeCust)' 
else 'dj.cust_cd is not null' 
end [Parameter]
from daily_job dj
where dj.job_date_time > getdate-7
and ds.cust_cd = isnull(@chargeCust, ds.cust_cd)

hth
Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

TroyG SSCrazy Points: 2633 More actions · Answer 3

Brilliant - thanks for both the replies, you have offered some ways around the issue I was after, and it is now working!

Thanks again - much appreciated!