?
Solved

SQL Case in Where Clause

Posted on 2009-12-21
8
Medium Priority
?
373 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:r270ba
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1000 total points
ID: 26096205
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
 
LVL 5

Assisted Solution

by:felipevidaurri
felipevidaurri earned 400 total points
ID: 26096526
where c.location = case when item_tracking_option = 'serialized' then 'tsp' else null end
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 600 total points
ID: 26096604
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:chapmandew
ID: 26096633
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
 
LVL 71

Expert Comment

by:Qlemo
ID: 26096656
... and   and quantity > 0    has to be appended to the where, of course.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26096685
yep...was only including the CASE part...
0
 

Author Comment

by:r270ba
ID: 26096732
Ok guys...thanks for the prompt responses!  Let me give them a try and I will let you know which works.
0
 

Accepted Solution

by:
r270ba earned 0 total points
ID: 26296242
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question