Re: cannot have subqueries in CONNECT BY clause

From: timkarnold <timkarnold_at_home.com>
Date: Tue, 20 Nov 2001 13:16:00 GMT
Message-ID: <kIsK7.39117$Ze5.21772105@news1.rdc1.md.home.com>

from Thomas Kyte

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.

"Alexander V. Silantiev" <silantiev_at_bashkortostan.ru> wrote in message news:9tagnc$a5d$1_at_poikc.bashnet.ru...
> Answer to me, please.
> Are there Oracle server version in that not exists
> restriction 'cannot have subqueries in CONNECT BY clause' ?
> Thanks in advance.

>

> Alexander.

>
> Received on Tue Nov 20 2001 - 07:16:00 CST