ORACLE 查询上级下级间关系

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;

 

ORACLE 查询上级下级间关系

标签：oracle   branch   .data   select   length   case   bank   path   replace