OraQA

The following SQL pattern can be used to find all the leaf path nodes and assign a number to its roots and branches.

CREATE TABLE emp (empno NUMBER, ename VARCHAR2(10), mgr VARCHAR2(10));
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (100, 'A', NULL);
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (200, 'C', 'A');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (300, 'H', 'C');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (400, 'E', 'A');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (500, 'N', 'E');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (600, 'E', 'B');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (700, 'P', 'O');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (800, 'O', 'F');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (900, 'J', 'O');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (1000, 'B', NULL);
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (1100, 'F', NULL);

-------------------------------------------------------10G SQL Solution-----------------------------------------------------

SQL> SELECT     LEVEL,
  2             SUBSTR (PATH,
  3                     INSTR (PATH, '/', 1, LEVEL) + 1,
  4                       INSTR (PATH, '/', 1, LEVEL + 1)
  5                     - INSTR (PATH, '/', 1, LEVEL)
  6                     - 1
  7                    ) ename,
  8             root,
  9             rn branch_num,
 10             DENSE_RANK () OVER (ORDER BY root) root_num
 11        FROM (SELECT PATH,
 12                     root,
 13                     ROWNUM rn
 14                FROM (SELECT     SYS_CONNECT_BY_PATH (ename, '/') || '/' PATH,
 15                                 CONNECT_BY_ROOT ename AS root,
 16                                 CONNECT_BY_ISLEAF leaf_flag
 17                            FROM emp a
 18                      START WITH mgr IS NULL
 19                      CONNECT BY PRIOR ename = mgr)
 20               WHERE leaf_flag = 1)
 21  CONNECT BY PRIOR PATH = PATH
 22         AND INSTR (PATH, '/', 1, LEVEL + 1) > 0
 23         AND PRIOR DBMS_RANDOM.STRING ('p', 10) IS NOT NULL;

     LEVEL ENA ROOT       BRANCH_NUM   ROOT_NUM
---------- --- ---------- ---------- ----------
         1 A   A                   1          1
         2 C   A                   1          1
         3 H   A                   1          1
         1 A   A                   2          1
         2 E   A                   2          1
         3 N   A                   2          1
         1 B   B                   3          2
         2 E   B                   3          2
         3 N   B                   3          2
         1 F   F                   4          3
         2 O   F                   4          3
         3 J   F                   4          3
         1 F   F                   5          3
         2 O   F                   5          3
         3 P   F                   5          3

15 rows selected.

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.