Monday, October 12, 2015

Oracle Tree connect_by_root

connect_by_root

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.

SELECT ename "Employee", CONNECT_BY_ROOT empno "Root", LEVEL,
SYS_CONNECT_BY_PATH(ename,'/') "Path" 
FROM scott.emp
WHERE level <= 3 AND deptno = 10 START WITH ename='KING'
CONNECT BY NOCYCLE PRIOR empno = mgr AND LEVEL <= 4
 
 

No comments:

Post a Comment