Oracle10g connect_by_root
The unary operator connect_by_root is only valid in
hierarchical queries. When a column is qualified with this
operator, Oracle returns the column value using data from the root
row. This operator is intended to extend the functionality of the
CONNECT BY [PRIOR] condition of hierarchical queries.
There is one restriction on the connect_by_root clause - this
operator cannot be specified in the START WITH condition or the
CONNECT BY condition.
SQL> SELECT ename "Employee", CONNECT_BY_ROOT empno
"Root", LEVEL,
2 SYS_CONNECT_BY_PATH(ename,'/') "Path" FROM
scott.emp
3 WHERE level <= 3 AND deptno = 10 START WITH
ename='KING'
4* CONNECT BY NOCYCLE PRIOR empno = mgr AND LEVEL
<= 4
Employee Root LEVEL Path
-------------- ----- --------- ------------------
KING 7839 1 /KING
CLARK 7839 2 /KING/CLARK
MILLER 7839 3 /KING/CLARK/MILLER |
