Most of us are familiar with the CONNECT BY syntax to navigate a hierarchy and the SYS_CONNECT_BY_PATH to see all of the nodes from root to leaf.
SQL> select SYS_CONNECT_BY_PATH(ename,'-') full_tree
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
FULL_TREE
---------------------------
-KING
-KING-BLAKE
-KING-BLAKE-JAMES
-KING-BLAKE-ALLEN
-KING-BLAKE-WARD
-KING-CLARK
-KING-CLARK-MILLER
...
But for many developers, that is where the knowledge of the capability ends. Dig a little deeper, there are a lot more goodies in there.
For example, want to sort the entries within each level? Easy! Just add ORDER BY SIBLINGS
SQL> select rpad(' ',level)||sys_connect_by_path(ename,'-'),
2 empno, dname
3 from emp e, dept d
4 where d.deptno = e.deptno
5 start with mgr is null
6 connect by prior empno = mgr
7 ORDER SIBLINGS BY ENAME
FULL_TREE EMPNO DNAME
--------------------------- ---------- --------------
-KING 7839 ACCOUNTING
-KING-BLAKE 7698 SALES
-KING-BLAKE-ALLEN 7499 SALES
-KING-BLAKE-JAMES 7900 SALES
-KING-BLAKE-WARD 7521 SALES
-KING-CLARK 7782 ACCOUNTING
-KING-CLARK-MILLER 7934 ACCOUNTING
...
Maybe you need to see for a given row, any of:
- What is the very root level of the hierarchy for that node?
- Am I on a leaf (no children) or a branch ?
- If my hierarchy data is poor, can I handle cyclic relationships and how do I detect them?
All of these are easy as well!
SQL> select connect_by_root ename root,
2 connect_by_isleaf leaf,
3 connect_by_iscycle cyc,
4 sys_connect_by_path(ename,'-') full
5 from emp
6 start with ename in ('KING','DENNIS')
7 connect by NOCYCLE prior empno = mgr;
ROOT LEAF CYC FULL
---------- ------ ------ -----------------------
KING 0 0 -KING
KING 0 0 -KING-JONES
KING 0 0 -KING-JONES-SCOTT
KING 1 0 -KING-JONES-SCOTT-ADAMS
KING 0 0 -KING-JONES-FORD
KING 1 0 -KING-JONES-FORD-SMITH
KING 0 0 -KING-BLAKE
KING 1 1 -KING-BLAKE-ALLEN
KING 1 0 -KING-BLAKE-WARD
KING 1 0 -KING-BLAKE-MARTIN
KING 1 0 -KING-BLAKE-TURNER
KING 1 0 -KING-BLAKE-JAMES
DENNIS 0 0 -DENNIS
DENNIS 0 0 -DENNIS-CHRIS
DENNIS 1 0 -DENNIS-CHRIS-CRAIG
Ho Ho Ho! Merry Christmas!
Learning is not a spectator sport 
Leave a reply to Jared Cancel reply