Full outer join gives error in pl/sql procedure in 10G
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Full outer join gives error in pl/sql procedure in 10G

  1. #1
    Join Date
    Dec 2003

    Full outer join gives error in pl/sql procedure in 10G

    Hi all,
    I am using following version of oracle.

    Oracle Database 10g Enterprise Edition Release - 64bi
    PL/SQL Release - Production
    CORE Production
    TNS for Solaris: Version - Production
    NLSRTL Version - Production

    I am using following construct query in a procedure.
    The tables tab3,tab4,tab5 and tab6 are equijoined and an inline view is created (tview). Tables tab7 and tab6 are equijoined and an inline view is created (tview2). Tview is full outer joined with tview2 and an inline view is created (tview8). Tab1 is left joined with tview8 to get the desired output.
    The query is shown below:

    cursor c_test (p1 nummer, p2 nummer)
    SELECT nummer nummer
    , eancode
    , begindatum
    , einddatum
    FROM tab1 t1
    , tab2 t2
    , (
    SELECT NVL(tview.nummer, tview2.nummer ) nummer
    , NVL(tview2.eancode, tview1.eancode) eancode
    , LEAST( NVL(tview2.begindatum,tview1.begindatum), NVL(tview1.begindatum,tview2.begindatum) ) begindatum
    , DECODE(tview2.einddatum
    , TO_DATE('31-12-9999','dd-mm-yyyy')
    , NULL
    , DECODE(tview1.einddatum
    , TO_DATE('31-12-9999','dd-mm-yyyy')
    , NULL
    , GREATEST( NVL(tview2.einddatum,tview1.einddatum)
    , NVL(tview1.einddatum,tview2.einddatum) )
    ) einddatum
    ( SELECT tview3.cllo_nummer cllo_nummer
    , tview3.ovze_nummer ovze_nummer
    , tview3.ean_adreskode ean_adreskode
    , MIN(tview3.begindatum) begindatum
    , MAX(tview3.einddatum) einddatum
    FROM (
    SELECT cllo_nummer
    , levr_nummer
    , ovze_nummer
    , ean_adreskode
    , MAX(begindatum) begindatum
    , MIN(einddatum) einddatum
    FROM (
    SELECT t3.nummer
    , t3.l_nummer
    , t3.o_nummer
    , t6.eancode
    , MIN( t3.begindatum) begindatum
    , LEAST( MAX(NVL(t3.einddatum ,TO_DATE('31-12-9999','dd-mm-yyyy')))
    , MAX(NVL(t4.bestelbaar_winkels_tm ,TO_DATE('31-12-9999','dd-mm-yyyy')))
    , MAX(NVL(t5.datum_leverbaar_tm ,TO_DATE('31-12-9999','dd-mm-yyyy'))) ) einddatum
    FROM tab3 t3
    , tab4 t4
    , tab5 t5
    , tab6 t6
    WHERE 1=1
    AND t3.nummer = :b_nummer
    AND t3.o_nummer = NVL(:b_o_nummer, t3.o_nummer)
    AND t3.nummer = t4.nummer
    AND t3.l_nummer = t4.l_nummer
    AND t3.nummer = t5.nummer(+)
    AND t6.nummer = t3.o_nummer
    AND t6.site_nummer IS NOT NULL
    AND ( t4.datum_leverbaar_tm >= TRUNC(SYSDATE)
    OR t4.datum_leverbaar_tm IS NULL )
    AND ( t5.bestelbaar_winkels_tm >= TRUNC(SYSDATE)
    OR t5.bestelbaar_winkels_tm IS NULL )
    AND ( t3.einddatum >= TRUNC(SYSDATE)
    OR t3.einddatum IS NULL )
    GROUP BY t3.nummer
    , t3.l_nummer
    , t3.o_nummer
    , t6.eancode
    ) tview6
    GROUP BY tview6.nummer
    , tview6.l_nummer
    , tview6.o_nummer
    , tview6.eancode
    ) tview3
    GROUP BY tv.nummer
    , tv.o_nummer
    , tv.eancode
    ) tview
    ( SELECT t7.nummer nummer
    , t8.nummero_nummer
    , t8.eancode
    , MIN(t7.begindatum_afroepbaar) begindatum
    , MAX(NVL(t7.einddatum_leverbaar,TO_DATE('31-12-9999','dd-mm-yyyy'))) einddatum
    FROM tab7 t7
    , tab6 t8
    WHERE 1=1
    AND ( t7.einddatum_leverbaar >=TRUNC(SYSDATE)
    OR t7.einddatum_leverbaar IS NULL )
    AND t7.cllo_nummer = :b_cllo_nummer
    AND t7.ovze_nummer = NVL(:b_ovze_nummer, t7.ovze_nummer)
    AND t8.nummer = cloa.ovze_nummer
    AND t8.site_nummer IS NOT NULL
    GROUP BY t7.nummer
    , t8.nummer
    , t8.eancode
    ) tview2
    ON tview.nummer = tview2.nummer
    AND tview.o_nummer = tview2.o_nummer
    ) tview8
    WHERE 1=1
    FROM tab2 t9
    WHERE t9.basis_art = t1.nummer )
    AND t1.vart_nasa_nummer = t2.nasa_nummer(+)
    AND t1.nummer = :b_nummer
    AND tview8.cllo_nummer(+) = t1.nummer
    AND tview8.nummer IS NOT NULL;

    the above cursor is used in a procedure to fetch data.

    The cursor query is returning proper data when executed from sql prompt but while executing it from pl/sql
    it gives error :

    ORA-00923: FROM keyword not found where expected
    The error is coming because of FULL OUTER JOIN used in the query. if i use left outer join or right outer join it runs successfully.but if I use full outer join, it gives above error.

    Can any one help me out?

    Ramchandra Jetwani

  2. #2
    Join Date
    Dec 2003
    After raising a tar with oracle, it is confirmed that it is an oracle bug. If we use bind variable in a query which is using Full outer join then it gives the error of From keyword not found where expected. The workaround that I use to solve the problem is instead of using full outer join on the inline views I first left joined the internal view union right join on the same internal views and the expected output was available.
    Ramchandra Jetwani

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width