Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query dependent on three fields

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-10-13 : 15:05:49
I am trying to do a select query like below...
SELECT     INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM, 
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR


But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.

I'm not sure how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.

Hope that makes sense.
Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-13 : 15:17:02
WHERE INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS' AND QTY_ALLOCATED <> 0 AND QTY_ON_HAND <> 0 AND Qty_On_ORDER <> 0

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-10-13 : 15:40:06
quote:
Originally posted by tkizer

WHERE INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS' AND QTY_ALLOCATED <> 0 AND QTY_ON_HAND <> 0 AND Qty_On_ORDER <> 0



Thanks for your reply Tara. I tried that combination also but then I get no records and there are records in there that have a 0 in two of the three columns. I do want it to return the records if one or two of the columns has a 0 I just don't want the records if all three columns have a 0.

Thanks,
Stacy
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-13 : 15:54:14
Sorry I didn't read your post carefully. I just looked at your code sample and saw the syntax issue.

WHERE INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS' AND (QTY_ALLOCATED <> 0 OR QTY_ON_HAND <> 0 OR Qty_On_ORDER <> 0)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -