Re: ORA-01437: cannot have join with CONNECT BY... but I want to find a workaround!

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 27 Feb 2004 10:25:22 -0500
Message-ID: <bNKdnaD25971_KLd4p2dnA@comcast.com>

"NoName" <nobody_at_nowhere.com> wrote in message news:c1nkp1$qmv$1_at_grillo.cs.interbusiness.it...
| Hello,
|
| I get this error using Oracle 8.1.7 (in Oracle 9 everything works fine).
| Since I'm developing applications for both versions of Oracle, I have to
| find another way to write my hierarchical query.
|
| Here's the query:
| ---------------------------------------
| select T2.num
| from T1,T2
| where T1.id=T2.id
| start with T1.job='PRESIDENT'
| connect by prior subjob=job
| ---------------------------------------
|
| Table T1 has columns job,subjob,id
| Table T2 has columns num,id
|
| The above query, as said, works fine in Oracle 9, but in Oracle 8 returns
| the ORA-01437 error (self explaining).
|
| I tried to get rid of this error, using a subquery, as in the following
| example:
| ---------------------------------------
| select * from
| (select T2.num,T1.job,T1.subjob
| from T1,T2
| where T1.id=T2.id)
| start with job='PRESIDENT'
| connect by prior subjob=job
| ---------------------------------------
| ...but Oracle 8 returns the same ORA-01437 error.
|
| So, people, if you like playing adventures, think about this one as a
| "Monkey Island" or "Leisure Suit Larry" puzzle... :-)
| Any clue?
| Thank you!
|
|

you're on the right track -- but you need to take the join out of the from-subquery

classic example:

SELECT emps.*

      ,mgrs.ename AS manager
  FROM (SELECT     RPAD('.', LEVEL * 2, '.') || ename AS employee
                  ,job
                  ,empno
                  ,mgr AS mgrno
              FROM emp
        CONNECT BY PRIOR empno = mgr
        START WITH mgr IS NULL) emps
      ,emp mgrs

 WHERE emps.mgrno = mgrs.empno(+)
/

;-{ mcs Received on Fri Feb 27 2004 - 09:25:22 CST