Need help with a Where clause in SQL Server query
I have a SQL Query where I need to limit the results set. I know the WHERE statement is incorrect, but gives an idea what I am looking for. I tried doing this in a CASE statement but I just could not get it. I am hoping someone can help me with this.
SELECT TOP (100) PERCENT dbo.WO010032.ITEMNMBR AS [Make Item], dbo.PK010033.ITEMNMBR AS [Item No], dbo.PK010033.QTY_ALLOWED_I AS PickQty, dbo.IV00300.BIN
FROM dbo.WO010032 RIGHT OUTER JOIN
dbo.PK010033 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.PK010033.MANUFACTUREORDER_I LEFT OUTER JOIN
dbo.IV00101 ON dbo.PK010033.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.IV00300 ON dbo.PK010033.ITEMNMBR = dbo.IV00300.ITEMNMBR AND dbo.PK010033.LOCNCODE = dbo.IV00300.LOCNCODE
WHERE IF dbo.WO010032.ITEMNMBR LIKE '60%' THEN dbo.IV00300.BIN NOT LIKE 'MFG%'
ELSE dbo.IV00300.BIN NOT LIKE 'BUF%'SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You can see when I run this query for Item Number 10.121b, The results show Bins that begin with MFG for a 60 series part.
SELECT TOP (100) PERCENT dbo.WO010032.ITEMNMBR AS [Make Item], dbo.PK010033.ITEMNMBR AS [Item No], dbo.PK010033.QTY_ALLOWED_I AS PickQty, dbo.IV00300.BIN
FROM dbo.WO010032 RIGHT OUTER JOIN
dbo.PK010033 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.PK010033.MANUFACTUREORDER_I LEFT OUTER JOIN
dbo.IV00101 ON dbo.PK010033.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.IV00300 ON dbo.PK010033.ITEMNMBR = dbo.IV00300.ITEMNMBR AND dbo.PK010033.LOCNCODE = dbo.IV00300.LOCNCODE
WHERE (dbo.WO010032.ITEMNMBR LIKE '60%') AND (dbo.IV00300.BIN NOT LIKE 'MFG%') AND (dbo.PK010033.ITEMNMBR = '10.121b') OR
(dbo.IV00300.BIN NOT LIKE 'BUF%') AND (dbo.PK010033.ITEMNMBR = '10.121b')SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think what you added above might have worked for me.
WHERE (dbo.WO010032.ITEMNMBR LIKE '60%' AND dbo.IV00300.BIN NOT LIKE 'MFG%') OR
(dbo.WO010032.ITEMNMBR NOT LIKE '60%' AND dbo.IV00300.BIN NOT LIKE 'BUF%')
I will do a bit more testing and report back.
WHERE (dbo.WO010032.ITEMNMBR LIKE '60%' AND dbo.IV00300.BIN NOT LIKE 'MFG%') OR
(dbo.WO010032.ITEMNMBR NOT LIKE '60%' AND dbo.IV00300.BIN NOT LIKE 'BUF%')
I will do a bit more testing and report back.
ASKER
I was trying to make this harder than it actually was. Thanks for this lesson.
You actually had it right with your original IF: I just translated that IF into the corresponding WHERE conditions.
ASKER
But, If dbo.WO010032.ITEMNMBR NOT LIKE '60%' then exclude Bins "NOT LIKE 'BUF%'," but include Bins "LIKE 'MFG%'"