1 Reply Latest reply: Mar 6, 2007 6:00 PM by 428652 RSS

    Ora-00604 and Ora-00904 in a With Clause

    431980
      Hi:
      If I use a “left outer join” in the then following SQL I get a result set, but when I use a “full outer join” I get ora-00604 and ora-00904. Please let me know why this is happening?

      Full Join Example:

      with XYZ as (
      select tp.field1 as APID1
      ,pay.field2 as APAMT1
      from Table1 tp join table2 pay on tp.id = pay.id
      where rownum < 10
      )
      , XYZ2 as (
      select Nvl(pd.field1,0) as PID
      , pd.field2 as PAMT
      from Table1 tp join table3 pd on tp.id = pd.id

      where tp.field1 = -1
      )
      select COALESCE(XYZ.APID1, xyz2.PID,0)as PNM
      , NVL(XYZ.APAMT1, 0) as XYZAMT
      , NVL(xyz2.PAMT, 0) as XYZ2AMT
      from XYZ full outer join XYZ2 on PID = APID1

      Note: the SQL in XYZ2 returns 0 rows.
      The error I get is :
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00904: "from$_subquery$_003"."QCSJ_C003008_12": invalid identifier


      Left Outer Join Example (This returns result):

      with XYZ as (
      select tp.field1 as APID1
      ,pay.field2 as APAMT1
      from Table1 tp join table2 pay on tp.id = pay.id
      where rownum < 10
      )
      , XYZ2 as (
      select Nvl(pd.field1,0) as PID
      , pd.field2 as PAMT
      from Table1 tp join table3 pd on tp.id = pd.id

      where tp.field1 = -1
      )
      select COALESCE(XYZ.APID1, xyz2.PID,0)as PNM
      , NVL(XYZ.APAMT1, 0) as XYZAMT
      , NVL(xyz2.PAMT, 0) as XYZ2AMT
      from XYZ left outer join XYZ2 on PID = APID1

      Note: the SQL in XYZ2 returns 0 rows.

      Thank you,