/*
Created by Cheon.jh
Creeted on 2012.05.30
ODI Version. 11.1.1.6
Object:
전체 Interface 작업 당 소스 테이블과
타겟 테이블의 관계를 나타낸다.
subquery를 사용하였을 경우,
그 내역까지 추적하여 테이블 단위로 나타낸다.
*/
SELECT
P.I_POP,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
P.POP_NAME,
S.LSCHEMA_NAME||'.'||ST.TABLE_NAME SOURCE_TAB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
AND P.I_TABLE IS NOT NULL
AND S.I_POP_SUB IS NULL
UNION ALL
SELECT
CONNECT_BY_ROOT I_POP,
CONNECT_BY_ROOT TARGET_TAB,
SUBSTR(SYS_CONNECT_BY_PATH(POP_NAME,'/'),2),
SLN||','||STN SOURCE_TAB
FROM
(
SELECT
P.I_FOLDER,
P.I_POP,
P.POP_NAME,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
TT.TABLE_NAME TTN,
DS.DS_ORDER,
S.TAB_ORD,
S.LSCHEMA_NAME SLN,
ST.TABLE_NAME STN,
S.I_POP_SUB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
)
WHERE STN IS NOT NULL
START WITH I_POP_SUB IS NOT NULL
CONNECT BY I_POP = PRIOR I_POP_SUB
ORDER BY 1 DESC,3
;
/*
Created by Cheon.jh
Creeted on 2012.05.30
ODI Version. 11.1.1.6
Object:
특정 테이블 정보를 입력하여
소스 테이블과 작업 Interface 명,
해당 테이블을 소스로 보고,
ETL 작업을 수행하는 타겟 테이블과
작업 Interface 명을 조회한다.
*/
SELECT CONNECT_BY_ROOT TARGET_TAB, LPAD(' ',(LEVEL-1)*3,' ')||SOURCE_TAB, POP_NAME, LEVEL
FROM (
SELECT
P.I_POP,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
P.POP_NAME,
S.LSCHEMA_NAME||'.'||ST.TABLE_NAME SOURCE_TAB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
AND P.I_TABLE IS NOT NULL
AND S.I_POP_SUB IS NULL
AND P.I_FOLDER IN ('1001','2001','5001','2002','3001','4001')
UNION ALL
SELECT
CONNECT_BY_ROOT I_POP,
CONNECT_BY_ROOT TARGET_TAB,
SUBSTR(SYS_CONNECT_BY_PATH(POP_NAME,'/'),2),
SLN||','||STN SOURCE_TAB
FROM
(
SELECT
P.I_FOLDER,
P.I_POP,
P.POP_NAME,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
TT.TABLE_NAME TTN,
DS.DS_ORDER,
S.TAB_ORD,
S.LSCHEMA_NAME SLN,
ST.TABLE_NAME STN,
S.I_POP_SUB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
AND P.I_FOLDER IN ('1001','2001','5001','2002','3001','4001')
)
WHERE STN IS NOT NULL
START WITH I_POP_SUB IS NOT NULL
CONNECT BY I_POP = PRIOR I_POP_SUB
ORDER BY 1
) A
START WITH
/*
TARGET_TAB = 'TT'
I_POP = '275002'
*/
CONNECT BY
NOCYCLE
TARGET_TAB = PRIOR SOURCE_TAB
;
/*
Created by Cheon.jh
Creeted on 2012.06.07
ODI Version. 11.1.1.6
Object:
특정 테이블 정보를 입력하여
소스 테이블과 작업 Interface 명,
해당 테이블을 소스로 보고,
ETL 작업을 수행하는 타겟 테이블과
작업 Interface 명을 조회한다.
*/
SELECT
POP_NAME,
-- LPAD(' ',(LEVEL-1),' ')||SOURCE_TAB SOURCE_TAB
LPAD(' ',DECODE(CONNECT_BY_ISLEAF,0,(4-LEVEL),CONNECT_BY_ISLEAF)-1,' ')||SOURCE_TAB SOURCE_TAB,
CONNECT_BY_ROOT TARGET_TAB ROOT_TAB,
LEVEL lvl, DECODE(CONNECT_BY_ISLEAF,0,(4-LEVEL),CONNECT_BY_ISLEAF)-1
FROM (
SELECT
P.I_POP,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
P.POP_NAME,
S.LSCHEMA_NAME||'.'||ST.TABLE_NAME SOURCE_TAB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
AND P.I_TABLE IS NOT NULL
AND S.I_POP_SUB IS NULL
AND P.I_FOLDER IN ('1001','2001','5001','2002','3001','4001')
UNION ALL
SELECT
CONNECT_BY_ROOT I_POP,
CONNECT_BY_ROOT TARGET_TAB,
SUBSTR(SYS_CONNECT_BY_PATH(POP_NAME,'/'),2),
SLN||','||STN SOURCE_TAB
FROM
(
SELECT
P.I_FOLDER,
P.I_POP,
P.POP_NAME,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
TT.TABLE_NAME TTN,
DS.DS_ORDER,
S.TAB_ORD,
S.LSCHEMA_NAME SLN,
ST.TABLE_NAME STN,
S.I_POP_SUB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
AND P.I_FOLDER IN ('1001','2001','5001','2002','3001','4001')
)
WHERE STN IS NOT NULL
START WITH I_POP_SUB IS NOT NULL
CONNECT BY I_POP = PRIOR I_POP_SUB
ORDER BY 1
) A
--START WITH
--TARGET_TAB = 'HTSBA01.FDHBW_RES_SETT'
--I_POP = '275002'
--POP_NAME = 'IF.FDHBW_RES_SETT'
CONNECT BY
NOCYCLE
TARGET_TAB = PRIOR SOURCE_TAB
;
/* 중간 단계에 팩트가 있는 거 제외*/
SELECT * FROM (
SELECT
POP_NAME,
-- LPAD(' ',(LEVEL-1),' ')||SOURCE_TAB SOURCE_TAB
LPAD(' ',DECODE(CONNECT_BY_ISLEAF,0,(3-LEVEL),CONNECT_BY_ISLEAF)-1,' ')||SOURCE_TAB SOURCE_TAB,
CONNECT_BY_ROOT TARGET_TAB ROOT_TAB,
LEVEL lvl, DECODE(CONNECT_BY_ISLEAF,0,(4-LEVEL),CONNECT_BY_ISLEAF)-1,
SYS_CONNECT_BY_PATH(SOURCE_TAB,'|') TABLE_PATH
FROM (
SELECT
P.I_POP,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
P.POP_NAME,
S.LSCHEMA_NAME||'.'||ST.TABLE_NAME SOURCE_TAB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
AND P.I_TABLE IS NOT NULL
AND S.I_POP_SUB IS NULL
AND P.I_FOLDER IN ('1001','2001','5001','2002','3001','4001')
AND P.POP_NAME NOT LIKE '%mig'
AND P.POP_NAME NOT LIKE '%0529'
UNION ALL
SELECT
CONNECT_BY_ROOT I_POP,
CONNECT_BY_ROOT TARGET_TAB,
SUBSTR(SYS_CONNECT_BY_PATH(POP_NAME,'/'),2),
SLN||','||STN SOURCE_TAB
FROM
(
SELECT
P.I_FOLDER,
P.I_POP,
P.POP_NAME,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
TT.TABLE_NAME TTN,
DS.DS_ORDER,
S.TAB_ORD,
S.LSCHEMA_NAME SLN,
ST.TABLE_NAME STN,
S.I_POP_SUB
FROM
SNP_POP P,
SNP_DATA_SET DS,
SNP_SOURCE_TAB S,
SNP_TABLE TT,
SNP_TABLE ST
WHERE 1=1
AND P.I_POP = DS.I_POP
AND DS.I_DATA_SET = S.I_DATA_SET
AND P.I_TABLE = TT.I_TABLE(+)
AND S.I_TABLE = ST.I_TABLE(+)
AND P.I_FOLDER IN ('1001','2001','5001','2002','3001','4001')
AND P.POP_NAME NOT LIKE '%mig'
AND P.POP_NAME NOT LIKE '%0529'
)
WHERE STN IS NOT NULL
START WITH I_POP_SUB IS NOT NULL
CONNECT BY I_POP = PRIOR I_POP_SUB
ORDER BY 1
) A
CONNECT BY
NOCYCLE
TARGET_TAB = PRIOR SOURCE_TAB
)
WHERE 1=1
AND TABLE_PATH NOT LIKE '|trgschema.f_%|%'
ORDER BY 3,1,2