Hi, I'm facing a bit of an struggle with this SQL statement. The one below results in the correct number of records (601). However, when I add the tables ITEM_C (Customer Part Number) using CUST_NUM and ITEM_NUM I get fewer records (526). The reason is because there are item numbers which do not have an ITEM_C record which is perfectly acceptable. But for the purposes of this report, I need to show all 601 records even if there is no ITEM_C record for a particular item number. I know there is an outer join somewhere. And I'm also almost sure that this SQL statement has been written incorrectly. Of course it isn't tuned either.
If anyone is able to assist me, I would be very grateful. Thanks in advance, Saira SELECT F.ORDER_DATE, B.SHIP_NUM, C.SORT_NAME, F.ORDER_NUM, B.PPS_NUM, A.ITEM_NUM, D.DESC_1, A.TO_ALLOC_QTY, A.SHIPPED_QTY, A.BO_QTY, E.UOM FROM SHIP_L A, SHIP B, CUST_SHP C, ITEM D, UOM E, ORD F WHERE A.SHIP_ID=B.SHIP_ID AND B.CUST_NUM=C.CUST_NUM AND B.SHIP_NUM=C.SHIP_NUM AND A.ITEM_NUM=D.ITEM_NUM AND D.STOCK_UOM_ID=E.UOM_ID AND B.ORD_ID=F.ORD_ID AND A.TO_ALLOC_QTY <> A.SHIPPED_QTY AND F.DIV_CODE='01' AND F.CANCELLED='N' AND A.WHSE_CODE='HL1' AND B.PPS_PRINTED='Y' AND F.DIV_CODE='01' AND F.CANCELLED='N' AND B.CUST_NUM='2' AND F.ORDER_DATE=SYSDATE-1 ORDER BY F.ORDER_DATE, C.SORT_NAME; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
