I trying to make a parent-child hierarchy tree in Oracle 11g but with this condition

1. every CODE is unique(no duplicate) and has main parent in PARENT_ID
2. but there is an exception when PARENT_ID IS LIKE '_999' then they have multiple parents that value is between ETC1 and ETC2
3. Max Level Limit is 4

This is the tree that I intend to do: Flow Tree

Below is the raw query for testing

with t as ( 
        select 'A1'    CODE, 'JOB_1_1' CODE_NM, 'A'    PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'A2'    CODE, 'JOB_1_2' CODE_NM, 'A'    PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'A3'    CODE, 'JOB_1_3' CODE_NM, 'A'    PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'B01'   CODE, 'JOB_2_1' CODE_NM, 'A1'   PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'B02'   CODE, 'JOB_2_2' CODE_NM, 'A2'   PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'B03'   CODE, 'JOB_2_3' CODE_NM, 'A3'   PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'C001'  CODE, 'JOB_3_1' CODE_NM, 'B01'  PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'C002'  CODE, 'JOB_3_2' CODE_NM, 'B02'  PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'C003'  CODE, 'JOB_3_3' CODE_NM, 'B03'  PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'D0001' CODE, 'JOB_4_1' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all 
        select 'D0002' CODE, 'JOB_4_2' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all 
        select 'D0003' CODE, 'JOB_4_3' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual 
    ) 
 select * from t

What I already try

SELECT LPAD(' ', 4*(LEVEL-1)) ||  CODE AS CODE, CODE_NM, PARENT_ID, LEVEL
FROM    t 
START WITH PARENT_ID = 'A'
CONNECT BY PRIOR  CODE = PARENT_ID 
; 

Result:

-- The result is as you expected, Data with 'C999' PARENT_CODE doesn't show Because not connected anywhere
| CODE       |CODE_NM | PARENT_ID|LEVEL|
| -----------| -------|----------|-----|
|A1          |JOB_1_1 | A        |1    |
|    B01     |JOB_2_1 | A1       |2    |
|        C001|JOB_3_1 | B01      |3    |
|A2          |JOB_1_2 | A        |1    |
|    B02     |JOB_2_2 | A2       |2    |
|        C002|JOB_3_2 | B02      |3    |
|A3          |JOB_1_3 | A        |1    |
|    B03     |JOB_2_3 | A3       |2    |
|        C003|JOB_3_3 | B03      |3    |

What I want is something like this:

| CODE           |CODE_NM | PARENT_ID|LEVEL|
| -----------    | -------|----------|-----|
|A1              |JOB_1_1 | A        |1    |
|    B01         |JOB_2_1 | A1       |2    |
|        C001    |JOB_3_1 | B01      |3    |
|           D0001|JOB_4_1 |          |4    |
|           D0002|JOB_4_2 |          |4    |
|           D0003|JOB_4_3 |          |4    |
|A2              |JOB_1_2 | A        |1    |
|    B02         |JOB_2_2 | A2       |2    |
|        C002    |JOB_3_2 | B02      |3    |
|           D0001|JOB_4_1 |          |4    |
|           D0002|JOB_4_2 |          |4    |
|           D0003|JOB_4_3 |          |4    |
|A3              |JOB_1_3 | A        |1    |
|    B03         |JOB_2_3 | A3       |2    |
|        C003    |JOB_3_3 | B03      |3    |
|           D0001|JOB_4_1 |          |4    |
|           D0002|JOB_4_2 |          |4    |
|           D0003|JOB_4_3 |          |4    |

All of the sources that I check have either have CODE duplicates in the table or just make a new Code to make different branching, Any input on this issue will be very helpful, and thanks in advance. My head already hurting because of this

CodePudding user response：

Add an OR condition to the CONNECT BY clause to check if PRIOR code BETWEEN etc1 and etc2:

with t as ( 
        select 'A1'    CODE, 'JOB_1_1' CODE_NM, 'A'    PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'A2'    CODE, 'JOB_1_2' CODE_NM, 'A'    PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'A3'    CODE, 'JOB_1_3' CODE_NM, 'A'    PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'B01'   CODE, 'JOB_2_1' CODE_NM, 'A1'   PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'B02'   CODE, 'JOB_2_2' CODE_NM, 'A2'   PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'B03'   CODE, 'JOB_2_3' CODE_NM, 'A3'   PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'C001'  CODE, 'JOB_3_1' CODE_NM, 'B01'  PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'C002'  CODE, 'JOB_3_2' CODE_NM, 'B02'  PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'C003'  CODE, 'JOB_3_3' CODE_NM, 'B03'  PARENT_ID, NULL ETC1, NULL ETC2 from dual union all 
        select 'D0001' CODE, 'JOB_4_1' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all 
        select 'D0002' CODE, 'JOB_4_2' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all 
        select 'D0003' CODE, 'JOB_4_3' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual 
    ) 
SELECT LPAD(' ', 4*(LEVEL-1)) ||  CODE AS CODE, CODE_NM, PARENT_ID, LEVEL
FROM    t 
START WITH PARENT_ID = 'A'
CONNECT BY
        PRIOR CODE = PARENT_ID
OR      PRIOR CODE BETWEEN ETC1 AND ETC2
; 

Outputs:

CODE	CODE_NM	PARENT_ID	LEVEL
A1	JOB_1_1	A	1
B01	JOB_2_1	A1	2
C001	JOB_3_1	B01	3
D0001	JOB_4_1	C999	4
D0002	JOB_4_2	C999	4
D0003	JOB_4_3	C999	4
A2	JOB_1_2	A	1
B02	JOB_2_2	A2	2
C002	JOB_3_2	B02	3
D0001	JOB_4_1	C999	4
D0002	JOB_4_2	C999	4
D0003	JOB_4_3	C999	4
A3	JOB_1_3	A	1
B03	JOB_2_3	A3	2
C003	JOB_3_3	B03	3
D0001	JOB_4_1	C999	4
D0002	JOB_4_2	C999	4
D0003	JOB_4_3	C999	4

If you want the PARENT_ID column to match with the prior level of the hierarchy then something like:

SELECT LPAD(' ', 4*(LEVEL-1)) ||  CODE AS CODE,
       CODE_NM,
       COALESCE(PRIOR code, parent_id) AS PARENT_ID,
       LEVEL
FROM    t 
START WITH PARENT_ID = 'A'
CONNECT BY
        PRIOR CODE = PARENT_ID
OR      PRIOR CODE BETWEEN ETC1 AND ETC2
;

db<>fiddle here