The Pipelines Newsletter informed me that the Oracle 9i documentation keeps the join feature in hierarchical queries secret. Well - altough I like this "enhancement" (hell - why did Oracle need 9 major versions for this ) I am not sure if I want to utilize this feature in a major project before having written release for this feature I can nail Oracle to... too many thing they promise to be ok didn't work in the last years, so why should this "hidden" feature do... ?

Maybe it was put off the release-feature list and they just forgot to tell the programmer???

If you want run a report where employees are placed in the hierarchical order and also see the department name for each employee, you need to run the query below:

SELECT level, e.ename, d.dname 
FROM emp e, dept d
WHERE e.deptno = d.deptno
START WITH  e.ename='WARD'
CONNECT BY PRIOR e.mgr=e.empno 

Unfortunately in Oracle 8i and below you will get a message: ORA-01437 cannot have join with CONNECT BY. This is a limitation of Oracle 8i.

You might utilize some workaround using views or temporary tables but the easiest way is to upgrade your database to Oracle 9i. Then you will get the desired result:

LEVEL      ENAME      DNAME

---------- ---------- -------------

1          WARD       SALES

2          BLAKE      SALES

3          KING       ACCOUNTING