jnsimex
asked on
SQL Query does not return the correct results
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?
SELECT
OEORDH.CUSTOMER,
OEORDH.BILNAME,
OEORDH.ORDNUMBER,
OEORDH.ORDDATE,
OEORDD.CATEGORY,
ICITEM.OPTFLD6,
ICITEM.PICKINGSEQ,
OEORDD.QTYBACKORD,
OEORDD.PRIUNTPRC,
ICILOC.QTYONHAND AS 'SC',
ICILOC_1.QTYONHAND AS 'MAR',
ICILOC_2.QTYONHAND AS 'OEM',
MIITEM.Q_STK AS 'MISYS', ICILOC.QTYONHAND+MIITEM.Q_
OEORDH.EXPDATE,
OEORDH."DESC",
OEORDH.REFERENCE,
OEORDD.LINENUM
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
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?
SELECT
OEORDH.CUSTOMER,
OEORDH.BILNAME,
OEORDH.ORDNUMBER,
OEORDH.ORDDATE,
OEORDD.CATEGORY,
ICITEM.OPTFLD6,
ICITEM.PICKINGSEQ,
OEORDD.QTYBACKORD,
OEORDD.PRIUNTPRC,
ICILOC.QTYONHAND AS 'SC',
ICILOC_1.QTYONHAND AS 'MAR',
ICILOC_2.QTYONHAND AS 'OEM',
MIITEM.Q_STK AS 'MISYS', ICILOC.QTYONHAND+MIITEM.Q_
OEORDH.EXPDATE,
OEORDH."DESC",
OEORDH.REFERENCE,
OEORDD.LINENUM
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(ICILOC.LOCATION='SC') AND (ICILOC_1.LOCATION='MAR') AND (ICILOC_2.LOCATION='OEM')
which might need to be :
(((ICILOC.LOCATION='SC') or (ICILOC.LOCATION='BPI') or (ICILOC.LOCATION='MEX') ) and (ICILOC_1.LOCATION='MAR') and (ICILOC_2.LOCATION='OEM') )
or:
((ICILOC.LOCATION in ('SC','BPI','MEX') AND (ICILOC_1.LOCATION='MAR') AND (ICILOC_2.LOCATION='OEM'))
But, what is ambiguous is, with the number of different sources for location, then which location are you talking about when you say you want it to include the additional locations ?