Re: Join , Where

From: Chip <ocp-dba_at_earthlink.net>
Date: Sat, 05 Jul 2003 00:11:16 -0700
Message-ID: <F001.005C36DE.20030704235923@fatcity.com>

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).

Received on Sat Jul 05 2003 - 02:11:16 CDT