WITH TMP AS       (SELECT TMP.ORG_NUM AS INST_ID,               TMP.UP_ORG_NUM AS PARENT_INST_ID,               LTRIM(SYS_CONNECT_BY_PATH(TMP.ORG_NUM, ','), ',') AS CODE_PATH        FROM (SELECT A.ORG_NUM,                     CASE WHEN A.ORG_TYP = '0' THEN NULL ELSE A.UP_ORG_NUM END AS UP_ORG_NUM,                     A.ORG_TYP                FROM TESTNOW A               WHERE A.DATA_DATE = 20180430) TMP        START WITH ORG_TYP = '0'        CONNECT BY PRIOR TMP.ORG_NUM = TMP.UP_ORG_NUM        UNION ALL        SELECT TMP.ORG_NUM AS INST_ID,               TMP.UP_ORG_NUM AS PARENT_INST_ID,               LTRIM(SYS_CONNECT_BY_PATH(TMP.ORG_NUM, ','), ',') AS CODE_PATH        FROM (SELECT A.ORG_NUM,                     CASE WHEN A.ORG_TYP = '9' THEN NULL ELSE A.UP_ORG_NUM END AS UP_ORG_NUM,                     A.ORG_TYP                FROM TESTNOW A               WHERE A.DATA_DATE = 20180430) TMP        START WITH ORG_TYP = '9'        CONNECT BY PRIOR TMP.ORG_NUM = TMP.UP_ORG_NUM)       SELECT SUBSTR(',' || A.CODE_PATH || ',', INSTR(',' || A.CODE_PATH, ',', 1, B.RN) + 1, INSTR(A.CODE_PATH || ',', ',', 1, B.RN) - INSTR(',' || A.CODE_PATH, ',', 1, B.RN)) AS INST_ID,             A.INST_ID AS SUB_INST_ID,             CASE               WHEN B.RN = 1 THEN                'root'               ELSE                SUBSTR(',' || A.CODE_PATH || ',', INSTR(',' || A.CODE_PATH, ',', 1, B.RN - 1) + 1, INSTR(A.CODE_PATH || ',', ',', 1, B.RN - 1) - INSTR(',' || A.CODE_PATH, ',', 1, B.RN - 1))             END UP_INST_ID,             CODE_PATH      FROM TMP A,           (SELECT ROWNUM RN            FROM DUAL            CONNECT BY ROWNUM < 10) B      WHERE LENGTH(A.CODE_PATH) - LENGTH(REPLACE(A.CODE_PATH, ',')) + 1 >= B.RN;

select connect_by_root t.bank_org_code hq_bank_org_cd,

connect_by_root t.branch_cd hq_branch_cd,

t.branch_cd,

t.busi_dt,

t.org_type_cd,

t.org_type_name,

t.branch_cd_org

from TESTDWH t

where t.busi_dt = date '2018-04-30'

start with org_type_cd = '0'

connect by nocycle prior t.branch_cd_org = t.branch_cd_up

union all

select connect_by_root t2.bank_org_code hq_bank_org_cd,

connect_by_root t2.branch_cd hq_branch_cd,

t2.branch_cd,

t2.busi_dt,

t2.org_type_cd,

t2.org_type_name,

t2.branch_cd_org

from TESTDWH t2

where t2.busi_dt = date '2018-04-30'

start with org_type_cd = '9'

connect by nocycle prior t2.branch_cd_org = t2.branch_cd_up;