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

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

Post by NoNam » Sat, 28 Feb 2004 23:40:27


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!
 
 
 

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

Post by Mark C. St » Sun, 29 Feb 2004 00:25:22


| 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

 
 
 

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

Post by NoNam » Sun, 29 Feb 2004 00:42:48

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


Grazie, Mark!
I understand, I'll try out later.

Regards