SQL Recursive Sub Query Factoring

Question

Consider a table with the below information. Table Data:

Code_ID	Name	Head_Name	Head_Rank	Report_To_Code
1	ABC	XYZ	07
2	DEF	BBB	01	1
3	GHI	ZZZ	02	1
4	JFK	XXX	10	2

Sample:

CREATE TABLE TEST_01 
   (  CODE_ID NUMBER(5), 
      NAME VARCHAR2(3), 
      HEAD_NAME VARCHAR2(3), 
      HEAD_RANK VARCHAR2(2), 
      REPORT_TO_CODE NUMBER(5)
   );
   
insert into test_01 (CODE_ID, NAME, HEAD_NAME, HEAD_RANK, REPORT_TO_CODE)
values (1, 'ABC', 'XYZ', '07', null);

insert into test_01 (CODE_ID, NAME, HEAD_NAME, HEAD_RANK, REPORT_TO_CODE)
values (2, 'DEF', 'BBB', '01', 1);

insert into test_01 (CODE_ID, NAME, HEAD_NAME, HEAD_RANK, REPORT_TO_CODE)
values (3, 'GHI', 'ZZZ', '02', 1);

insert into test_01 (CODE_ID, NAME, HEAD_NAME, HEAD_RANK, REPORT_TO_CODE)
values (4, 'JFK', 'XXX', '10', 2);

commit;

Our final Output need to stored as an hierarchial structure as shown below.

Code_ID	Name	Head_Name	Head_Rank	L1_Code	L2_Code	L3_Code	..	L12_Code	R_ID	LVL_CNT
1	ABC	XYZ	07						1	0
1	ABC	XYZ	07	2					2	1
1	ABC	XYZ	07	2	4				4	2
1	ABC	XYZ	07	3					3	1
2	DEF	BBB	01						2	0
2	DEF	BBB	01	4					4	1
3	GHI	ZZZ	02						3	0
4	JFK	XXX	10						4	0

No one reports to 3 or 4, so they have only one level. Here, R_ID -> Takes the last CODE_ID of the hierarchy. LVL_CNT -> Takes the Maximum number of hierarchial count the R_ID has with respect to the main CODE_ID.

All the reportees to main code, need to be traced back with a level count. The Max can be 12.

We have developed using multiple unions but that has made the process very slow as we deal with lots of data on monthly basis. Sample Code:

SELECT
           T01.CODE_ID
       ,   T01.NAME
       ,   T01.HEAD_NAME
       ,   T01.HEAD_RANK
       ,   NULL as L1_CODE
       ,   NULL as L2_CODE
       ,   NULL as L3_CODE
       ,   T01.CODE_ID
       ,   0
      FROM    test_01     T01
UNION
SELECT
           T01.REPORT_TO_CODE
       ,   TT.NAME
       ,   TT.HEAD_NAME
       ,   TT.HEAD_RANK
       ,   T01.CODE_ID as L1_CODE
       ,   NULL as L2_CODE
       ,   NULL as L3_CODE
       ,   T01.CODE_ID
       ,   1
      FROM    test_01     T01
            , test_01     TT
      WHERE T01.REPORT_TO_CODE = TT.CODE_ID
        AND T01.REPORT_TO_CODE <> T01.CODE_ID
UNION
SELECT
           T01.REPORT_TO_CODE
       ,   TT.NAME
       ,   TT.HEAD_NAME
       ,   TT.HEAD_RANK
       ,   T01.CODE_ID as L1_CODE
       ,   T02.CODE_ID as L2_CODE
       ,   NULL as L3_CODE
       ,   T02.CODE_ID
       ,   2
      FROM    test_01     T01
            , test_01     T02
            , test_01     TT
      WHERE T01.REPORT_TO_CODE = TT.CODE_ID
        AND T02.REPORT_TO_CODE =  T01.CODE_ID
        AND T01.REPORT_TO_CODE <> T01.CODE_ID
        AND T02.REPORT_TO_CODE <> T02.CODE_ID
UNION
SELECT
           T01.REPORT_TO_CODE
       ,   TT.NAME
       ,   TT.HEAD_NAME
       ,   TT.HEAD_RANK
       ,   T01.CODE_ID as L1_CODE
       ,   T02.CODE_ID as L2_CODE
       ,   T03.CODE_ID as L3_CODE
       ,   T03.CODE_ID
       ,   3
      FROM    test_01     T01
            , test_01     T02
            , test_01     T03
            , test_01     TT
      WHERE T01.REPORT_TO_CODE = TT.CODE_ID
        AND T02.REPORT_TO_CODE = T01.CODE_ID
        AND T03.REPORT_TO_CODE = T02.CODE_ID
        AND T01.REPORT_TO_CODE <> T01.CODE_ID
        AND T02.REPORT_TO_CODE <> T02.CODE_ID
        AND T03.REPORT_TO_CODE <> T03.CODE_ID

I would like to know if there is any easier way to achieve the same result using connect by prior or maybe joins with less unions then I would be very happy and thankful.

Answer 1

You can use a recursive sub-query factoring clause:

WITH rsqfc ( code_id, name, head_name, head_rank, report_to_code, l1_code, l2_code, l3_code, r_id, lvl_cnt ) AS (
  SELECT code_id,
         name,
         head_name,
         head_rank,
         report_to_code,
         CAST( NULL AS NUMBER ),
         CAST( NULL AS NUMBER ),
         CAST( NULL AS NUMBER ),
         code_id,
         0
  FROM   table_name
UNION ALL
  SELECT r.code_id,
         r.name,
         r.head_name,
         r.head_rank,
         r.report_to_code,
         CASE lvl_cnt
         WHEN 0
         THEN t.code_id
         ELSE r.l1_code
         END,
         CASE lvl_cnt
         WHEN 1
         THEN t.code_id
         ELSE r.l2_code
         END,
         CASE lvl_cnt
         WHEN 2
         THEN t.code_id
         ELSE r.l3_code
         END,
         t.code_id,
         lvl_cnt + 1
  FROM   table_name t
         INNER JOIN rsqfc r
         ON ( r.r_id = t.report_to_code )
)
SELECT *
FROM   rsqfc
ORDER BY code_id, l1_code NULLS FIRST, l2_code NULLS FIRST, l3_code NULLS FIRST;

Which, for the sample data:

CREATE TABLE table_name ( Code_ID, Name, Head_Name, Head_Rank, Report_To_Code ) AS
SELECT 1, 'ABC', 'XYZ', '07', NULL FROM DUAL UNION ALL
SELECT 2, 'DEF', 'BBB', '01', 1 FROM DUAL UNION ALL
SELECT 3, 'GHI', 'ZZZ', '02', 1 FROM DUAL UNION ALL
SELECT 4, 'JFK', 'XXX', '10', 2 FROM DUAL;

Outputs:

CODE_ID | NAME | HEAD_NAME | HEAD_RANK | REPORT_TO_CODE | L1_CODE | L2_CODE | L3_CODE | R_ID | LVL_CNT
------: | :--- | :-------- | :-------- | -------------: | ------: | ------: | ------: | ---: | ------:
      1 | ABC  | XYZ       | 07        |           null |    null |    null |    null |    1 |       0
      1 | ABC  | XYZ       | 07        |           null |       2 |    null |    null |    2 |       1
      1 | ABC  | XYZ       | 07        |           null |       2 |       4 |    null |    4 |       2
      1 | ABC  | XYZ       | 07        |           null |       3 |    null |    null |    3 |       1
      2 | DEF  | BBB       | 01        |              1 |    null |    null |    null |    2 |       0
      2 | DEF  | BBB       | 01        |              1 |       4 |    null |    null |    4 |       1
      3 | GHI  | ZZZ       | 02        |              1 |    null |    null |    null |    3 |       0
      4 | JFK  | XXX       | 10        |              2 |    null |    null |    null |    4 |       0

db<>fiddle here