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 10.1.0.5.0 - 64bi
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for Solaris: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - 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)
is
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
FROM
( 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
FULL OUTER JOIN
( 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
AND NOT EXISTS ( SELECT ''
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?
Thanks,
Ram
