SQL Case in Where Clause

I have a query, listed below, that I need to place a CASE statement in the WHERE clause.  

I need to match two fields a.location=c.location IF c.item_tracking_option = 'serialized".  If item_tracking_location does not equal "serialized" then I do not want to match the two location fields.

I was trying something like this

where c.location = case when item_tracking_option = 'serialized' then c.location='tsp' else null end

but it is not working.
select a.item_number, item_description, item_class_code, a.location, a.bin, case when item_tracking_option = 'none' then a.quantity when item_tracking_option = 'serial numbers' then c.qty end as quantity, selling_u_of_m, item_tracking_option, xboxupc, serial_num, case when a.bin like '%.p%' and a.bin not like 'te%' then substring(a.bin, 6,3) when a.bin like 'wh%' then substring(a.bin, 6,2)  else a.bin end as section, case when a.bin like 'wh%' then substring(a.bin, 4,1) end as pickable from spvinventorymultibin a
join spvitemmaster b
on a.item_number=b.item_number
left join spvinventoryserialnum c
on a.item_number=c.item_number and a.bin=c.bin
where quantity>0 and a.location='tsp' 
order by a.location, bin, a.item_number

Open in new window

r270baAsked:
Who is Participating?
 
r270baConnect With a Mentor Author Commented:
I ended up not completely changing the query so that the case would not be necessary.  I am going to split points based on number of responses.
0
 
chapmandewConnect With a Mentor Commented:
where
case when c.item_tracking_option = 'serialized' and a.location=c.location then 1
when isnull(c.item_tracking_option,'') <> 'serialized' then 1 else 0 end
0
 
felipevidaurriConnect With a Mentor Commented:
where c.location = case when item_tracking_option = 'serialized' then 'tsp' else null end
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
where quantity>0 and (item_tracking_option != 'serialized' or a.location='tsp')

felipevodaurri's version has to look like this, the above is wrong:

where  quantity>0 and c.location = case when item_tracking_option = 'serialized' then 'tsp' else c.location end
0
 
chapmandewCommented:
sorry..forgot something:

where case when c.item_tracking_option = 'serialized' and a.location=c.location then 1
when isnull(c.item_tracking_option,'') <> 'serialized' then 1 else 0 end = 1
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
... and   and quantity > 0    has to be appended to the where, of course.
0
 
chapmandewCommented:
yep...was only including the CASE part...
0
 
r270baAuthor Commented:
Ok guys...thanks for the prompt responses!  Let me give them a try and I will let you know which works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.