another connect by coolness

another thing missing from the press releases ;)

in 8i and before:

ops$tkyte_at_ORA817.US.ORACLE.COM> select lpad('*',level,'*') || ename ename, dname   2 from emp , dept
  3 where emp.deptno = dept.deptno
  4 start with mgr is null
  5 connect by prior empno = mgr
  6 /
from emp , dept

     *
ERROR at line 2:
ORA-01437: cannot have join with CONNECT BY

in 9i:

  1 select lpad('*',level,'*') || ename ename, dnam   2 from emp , dept
  3 where emp.deptno = dept.deptno
  4 start with mgr is null
  5* connect by prior empno = mgr
scott_at_TKYTE901.US.ORACLE.COM> /

ENAME                          DNAME
------------------------------ --------------


*KING                          ACCOUNTING

**JONES                        RESEARCH

***SCOTT                       RESEARCH

****ADAMS                      RESEARCH

***FORD                        RESEARCH

****SMITH                      RESEARCH

**CLARK                        ACCOUNTING

***MILLER                      ACCOUNTING

**BLAKE                        SALES

***ALLEN                       SALES

***WARD                        SALES

***JAMES                       SALES

***TURNER                      SALES

***MARTIN                      SALES

14 rows selected.

so, in addition to ORDER SIBLINGS BY to order a hierarchy and the sys_connect_by_path to get the trail back to your root of the hirearchy, you can JOIN with connect by.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp