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

From: NoName <nobody_at_nowhere.com>
Date: Fri, 27 Feb 2004 15:40:27 +0100
Message-ID: <c1nkp1$qmv$1@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! Received on Fri Feb 27 2004 - 08:40:27 CST