How to implement CONNECT BY features

ORACLE 11g release 2 introduced the recursive WITH clause as an alternative to the well known CONNECT BY clause.

This Blog entry shows how to implement all the CONNECT BY features using the new recursive WITH clause.

---

Leaf Nodes

Using CONNECT BY the pseudo column CONNECT_BY_ISLEAF can be used to determine whether a node is a leaf node (has no children) or an inner node. The value of the pseudo column CONNECT_BY_ISLEAF is “0” for an inner node and “1” for leaf nodes.

SELECT LEVEL, ename, CONNECT_BY_ISLEAF leaf, empno, mgr
  FROM emp e
 WHERE deptno = 10
CONNECT BY PRIOR empno = mgr
 START WITH mgr is NULL
 ORDER SIBLINGS BY ename
/

LEVEL      ENAME      LEAF       EMPNO      MGR
---------- ---------- ---------- ---------- ----------
         1 KING                0       7839
         2 CLARK               0       7782       7839
         3 MILLER              1       7934       7782

Recursive WITH clause does not offer a built-in function that provides this information. Easiest way to get the information is using a subquery (in-/outside the WITH clause) that counts the number of child nodes.

WITH emps (ename, empno, mgr, lvl)
       AS (SELECT ename, empno, mgr, 1 AS lvl
             FROM emp
            WHERE mgr IS NULL
           UNION ALL
           SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
             FROM            emps m
                  INNER JOIN emp  e ON (e.mgr = m.empno)
            WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sorting
SELECT e.lvl, e.ename
     , 1 - LEAST((SELECT COUNT(*) FROM emp x WHERE x.mgr = e.empno),1) leaf
     , e.empno, e.mgr
  FROM emps e
 ORDER BY e.sorting
/

LEVEL      ENAME       LEAF      EMPNO       MGR
---------- ---------- ---------- ---------- ----------
         1 KING                0       7839
         2 CLARK               0       7782       7839
         3 MILLER              1       7934       7782

If the SEARCH clause uses DEPTH_FIRST, the analytic function LEAD can be used to determine whether the column LVL of next row in the result set has a higher value than the current row. If so, the current row is not a leaf otherwise the current row is a leaf.

WITH emps (ename, empno, mgr, lvl)
       AS (SELECT ename, empno, mgr, 1 AS lvl
             FROM emp
            WHERE mgr IS NULL
           UNION ALL
           SELECT e.ename, e.empno, e.mgr, m.lvl + 1 AS lvl
             FROM            emps m
                  INNER JOIN emp  e ON (e.mgr = m.empno)
            WHERE e.deptno = 10)
SEARCH DEPTH FIRST BY ename SET sortingSELECT lvl
      ,ename
      ,CASE
          WHEN lvl - LEAD(lvl) OVER (ORDER BY sorting) < 0 
             THEN 0 
          ELSE 1 
       END AS leaf 
     ,empno
     ,mgr 
  FROM emps 
 ORDER BY sorting 
/ 

LVL        ENAME      LEAF       EMPNO      MGR 
---------- ---------- ---------- ---------- ---------- 
         1 KING                0       7839 
         2 CLARK               0       7782       7839 
         3 MILLER              1       7934       7782