I have an SQL Query is not returning orders that have items that are located in certain locations:
In the Order Details, when some of the items that are not residing in the Locations (SC,OEM,MAR) in the WHERE Portion of the query, the order does not show up. Some of the other locations are BPI or MEX.
How do I modify this query to include Orders with Items from the Locations SC,OEM,MAR along with locations BPI or MEX?
ICILOC.QTYONHAND AS 'SC',
ICILOC_1.QTYONHAND AS 'MAR',
ICILOC_2.QTYONHAND AS 'OEM',
MIITEM.Q_STK AS 'MISYS', ICILOC.QTYONHAND+MIITEM.Q_STK+ICILOC_1.QTYONHAND+ICILOC_2.QTYONHAND AS 'QUANTITY', OEORDH.PONUMBER,
FROM NU2001.dbo.ICILOC ICILOC, NU2001.dbo.ICILOC ICILOC_1, NU2001.dbo.ICILOC ICILOC_2, NU2001.dbo.ICITEM ICITEM, NU2001.dbo.MIITEM MIITEM, NU2001.dbo.OEORDD OEORDD, NU2001.dbo.OEORDH OEORDH
WHERE OEORDH.ORDUNIQ = OEORDD.ORDUNIQ AND OEORDD.ITEM = ICITEM.ITEMNO AND OEORDD.LOCATION = ICILOC.LOCATION AND OEORDD.ITEM = ICILOC.ITEMNO AND ICILOC.ITEMNO = MIITEM.SALES AND ICILOC.ITEMNO = ICILOC_1.ITEMNO AND ICILOC_2.ITEMNO = ICILOC.ITEMNO AND ICITEM.AUDTORG = ICILOC.AUDTORG AND ((OEORDD.QTYBACKORD>$0) AND (ICILOC.LOCATION='SC') AND (ICILOC_1.LOCATION='MAR') AND (ICILOC_2.LOCATION='OEM'))
ORDER BY ICITEM.OPTFLD6, OEORDH.BILNAME, OEORDH.ORDNUMBER