Re: another connect by coolness

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9ikn6j0137d_at_drn.newsguy.com...
> 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
>

AT LAST!!!!!!!! It took Oracle 4 major releases to resolve this problem/limitation, but they did resolve it.
Boy, this should have been possible 12 years ago, and I wouldn't have been on the brink of total collapse at that time.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Thu Jul 12 2001 - 14:37:35 CDT