From ocp-dba@earthlink.net Sat, 05 Jul 2003 00:11:16 -0700 From: Chip Date: Sat, 05 Jul 2003 00:11:16 -0700 Subject: Re: Join , Where Message-ID: MIME-Version: 1.0 Content-Type: text/plain Oracle 9i added SQL 1999 support, which includes OUTER JOIN. In SQL 92 syntax the where clause can have both the join relationships and filter conditions. In SQL 1999 syntax the where clause only contains filter conditions. Note: Oracle 9i allows both SQL92 and SQL 1999 syntax to peacefully coexist in the same statement (crazy to read). Using the Oracle 9i HR example schema this query works: SELECT country_name , postal_code , department_name , employee_id , job_title FROM countries LEFT OUTER JOIN locations ON countries.country_id = locations.country_id LEFT OUTER JOIN departments ON locations.location_id = departments.location_id LEFT OUTER JOIN employees ON employees.department_id = departments.department_id AND employees.manager_id = departments.manager_id LEFT OUTER JOIN jobs ON jobs.job_id = employees.job_id Along the way I noticed that changing LEFT to RIGHT on a line or two caused an intersection to occur in the result set (effectively becoming an INNER JOIN). Just wondering if this from clause works: FROM polinesrc LEFT OUTER JOIN poline ON polinesrc.company = poline.company AND polinesrc.po_code = poline.po_code AND polinesrc.po_number = poline.po_number AND polinesrc.po_release = poline.po_release AND polinesrc.line_nbr = poline.line_nbr LEFT OUTER JOIN popivuf ON poline.item = popivuf.item AND poline.procure_group = popivuf.procure_group AND poline.vendor = popivuf.vendor LEFT OUTER JOIN itemloc ON poline.company = itemloc.company AND poline.location = itemloc.location AND poline.item = itemloc.item LEFT OUTER itemmast ON itemloc.item = itemmast.item Have Fun :) Teresita Castro wrote: I run the query and it run even though in Rapid SQL send the next error: unexpected token:LEFT My questions is what is the difference between where an join in Oracle, what is better and why? >>> [EMAIL PROTECTED] 07/02/03 06:55PM >>> Hi !! (I am using Oracle 9.2) I am trying to run the next query but I received an error in the from statement. SELECT POLINE.ITEM AS CVEART, POLINESRC.QUANTITY AS CANT_PE, ROUND( ITEMLOC.SOH_QTY - ( ITEMLOC.ALLOC_QTY + ITEMLOC.IN_PROC_QTY), 2) AS EXIS_BOD, CASE WHEN POLINE.ENT_BUY_UOM='KG' THEN 'K' WHEN POLINE.ENT_BUY_UOM='UNID' THEN 'P' WHEN SUBSTR(POLINE.ENT_BUY_UOM,1,1)='C' THEN 'C' END UNIDAD, SUBSTR(POLINESRC.REQ_LOCATION,4,2) as TIENDA, CASE SUBSTR(POLINE.ENT_BUY_UOM,1,1) WHEN SUBSTR(ITEMMAST.ALT_UOM_01,1,1) THEN ITEMMAST.ALT_UOM_CONV_01 WHEN SUBSTR(ITEMMAST.ALT_UOM_02,1,1) THEN ITEMMAST.ALT_UOM_CONV_02 WHEN SUBSTR(ITEMMAST.ALT_UOM_03,1,1) THEN ITEMMAST.ALT_UOM_CONV_03 END EL_CANT_EM, POLINE.ENT_BUY_UOM AS EL_UNID_EM, POLINE.DESCRIPTION AS EL_DESCRIP, POLINE.PURCH_MAJCL AS EL_NO_FAM, CASE POLINE.TAX_CODE WHEN 'IVA 0%' THEN POLINE.TAXBL_UNT_CST WHEN 'IVA 10%' THEN POLINE.TAXBL_UNT_CST*1.1 WHEN 'IVA 15%' THEN POLINE.TAXBL_UNT_CST*1.15 ELSE ENT_UNIT_CST END EL_COSTO, POLINESRC.REQ_DEL_DATE AS FECHA, POLINE.VENDOR AS EL_NO_PROV, ITEMLOC.USER_FIELD_N2 AS FOR_SUR, ITEMLOC.USER_FIELD3 AS TIPO_ALMAC, POLINE.PO_NUMBER AS EL_NU_PEDI, CASE POLINE.COMPANY WHEN '2000' THEN POPIVUF.PIV_USR_FLD_01 WHEN '2001' THEN POPIVUF.PIV_USR_FLD_02 WHEN '2002' THEN POPIVUF.PIV_USR_FLD_03 ELSE '0' END EL_PAC_PRO FROM POLINESRC LEFT OUTER JOIN POPIVUF RIGHT OUTER JOIN POLINE ON POPIVUF.ITEM = POLINE.ITEM AND POPIVUF.PROCURE_GROUP = POLINE.PROCURE_GROUP AND POPIVUF.VENDOR = POLINE.VENDOR ON POLINESRC.COMPANY = POLINE.COMPANY AND POLINESRC.PO_CODE = POLINE.PO_CODE AND POLINESRC.PO_NUMBER = POLINE.PO_NUMBER AND POLINESRC.PO_RELEASE = POLINE.PO_RELEASE AND POLINESRC.LINE_NBR = POLINE.LINE_NBR LEFT OUTER JOIN ITEMLOC LEFT OUTER JOIN ITEMMAST ON ITEMLOC.ITEM = ITEMMAST.ITEM ON POLINE.COMPANY = ITEMLOC.COMPANY AND POLINE.LOCATION = ITEMLOC.LOCATION AND POLINE.ITEM = ITEMLOC.ITEM WHERE POLINE.PO_CODE='01' AND POLINESRC.REQ_DEL_DATE = sysdate ORDER BY POLINESRC.REQ_DEL_DATE What I was trying to do is made the next joins ( the first row are the tables, next rows are the fields of the join) POPOIVUF-POLINE ( all rows from POLINE) PROCURE_GROUP ITEM VENDOR POLINESRC-POLINE (all rows from POLINESRC) COMPANY PO_CODE PO_NUMBER PO_RELEASE LINE_NBR ITEMLOC-POLINE (all rows from POLINE) COMPANY LOCATION ITEM ITEMLOC-ITEMMAST (all rows from ITEMLOC) ITEM I tried to do this whit TOAD Modeler, but it used where instead of join. In Oracle are the same? WHERE ( (poline.company = pol.company) AND (poline.po_code = pol.po_code) AND (poline.po_number = pol.po_number) AND (poline.po_release = pol.po_release) AND (poline.line_nbr = pol.line_nbr) AND (popivuf.procure_group = poline.procure_group) AND (popivuf.item = poline.item) AND (popivuf.vendor = poline.vendor) AND (itemloc.item = itemmast.item_group) AND (itemloc.company = poline.company) AND (poline.LOCATION = itemloc.LOCATION) AND (itemloc.item = poline.item) ) I was running the query in SQL Server 2000, and it works fine there, what I am doing wrong ??? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chip 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).