FROM ....., ITEM_C C WHERE ......... AND A.ITEM_NUM = C.ITEM_NUM(+) AND B.CUST_NUM = C.CUST_NUM(+)
Regards Naveen > -----Original Message----- > From: Saira Somani [mailto:[EMAIL PROTECTED] > Sent: Friday, June 06, 2003 8:55 PM > To: Multiple recipients of list ORACLE-L > Subject: SQL statement problem - outer join where? > > > 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). > DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata 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).
