Re: Join , Where

[Teresita Castro]

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 ???