troubleshooting Question

SQL Query does not return the correct results

Avatar of jnsimex
jnsimex asked on
DatabasesOracle DatabaseSQL
2 Comments1 Solution339 ViewsLast Modified:
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_STK+ICILOC_1.QTYONHAND+ICILOC_2.QTYONHAND AS 'QUANTITY', OEORDH.PONUMBER,
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros