통합 매핑 정의서 작업 리스트 참조 쿼리
2012.09.07 변경 사항
1. 프로시저 포함
2. output format 정리
/* 2012.09.07
created by cheon, jh.
통합 매핑 정의서
*/
WITH A AS (
SELECT
P.I_FOLDER,
P.I_POP,
P.POP_NAME,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
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
I_FOLDER,
I_POP,
SUBSTR(SYS_CONNECT_BY_PATH(POP_NAME,'/'),2),
CONNECT_BY_ROOT TARGET_TAB,
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
UNION ALL
SELECT I_FOLDER,
I_TRT,
TRT_NAME,
NULL,
NULL
FROM SNP_TRT T
ORDER BY 1 DESC,3
),
B AS (
SELECT '' 구분,
'' 주제영역,
POP_NAME "프로시져/인터페이스(작업흐름)",
'"'||LISTAGG(SOURCE_TAB ,'
') WITHIN GROUP(ORDER BY SOURCE_TAB)||'"' "소스테이블",
TARGET_TAB "타겟테이블",
PROJECT_NAME ||'/'||FOLDER_NAME "위치"
FROM A,
SNP_FOLDER F,
SNP_PROJECT P
WHERE F.I_PROJECT = P.I_PROJECT
AND A.I_FOLDER = F.I_FOLDER
GROUP BY
POP_NAME,
TARGET_TAB,
PROJECT_NAME,
FOLDER_NAME
)
SELECT
구분,
주제영역,
SCEN_NAME "시나리오명(실행단위)",
PACK_NAME "패키지명(작업단위)",
"프로시져/인터페이스(작업흐름)",
"소스테이블",
"타겟테이블",
"위치"
FROM B, (
SELECT MAX(SC.SCEN_NO) SCEN_NO, SCEN_NAME, PACK_NAME, SSS.STEP_NAME
FROM SNP_SCEN SC,
SNP_SCEN_STEP SSS,
SNP_PACKAGE P
WHERE SC.I_PACKAGE IS NOT NULL
AND SC.SCEN_NO = SSS.SCEN_NO
AND STEP_TYPE IN ('F', 'T')
AND P.I_PACKAGE(+) = SC.I_PACKAGE
GROUP BY
SCEN_NAME,
PACK_NAME,
SSS.STEP_NAME
) C
WHERE B."프로시져/인터페이스(작업흐름)" = TRIM(C.STEP_NAME(+))
ORDER BY 8,5,4;
2012.06.07
WITH A AS (
SELECT
P.I_FOLDER,
P.I_POP,
P.POP_NAME,
P.LSCHEMA_NAME||'.'||TT.TABLE_NAME TARGET_TAB,
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
I_FOLDER,
I_POP,
SUBSTR(SYS_CONNECT_BY_PATH(POP_NAME,'/'),2),
CONNECT_BY_ROOT TARGET_TAB,
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 DESC,3
),
B AS (
SELECT
-- A.I_POP, A.I_FOLDER,
POP_NAME "프로시져/인터페이스(작업흐름)",
'"'||LISTAGG(SOURCE_TAB ,'
') WITHIN GROUP(ORDER BY SOURCE_TAB)||'"' "소스테이블",
TARGET_TAB "타겟테이블",
PROJECT_NAME ||'/'||FOLDER_NAME "위치"
FROM A,
SNP_FOLDER F,
SNP_PROJECT P
WHERE F.I_PROJECT = P.I_PROJECT
AND A.I_FOLDER = F.I_FOLDER
GROUP BY
-- A.I_POP,
-- A.I_FOLDER,
POP_NAME,
TARGET_TAB,
PROJECT_NAME,
FOLDER_NAME
)
SELECT SCEN_NAME "시나리오명(실행단위)",PACK_NAME "패키지명(작업단위)", B.* ,
'일배치' "처리주기",
'03:00 AM' "처리시간",
'D-1' "처리범위"
FROM B, (
SELECT MAX(SC.SCEN_NO) SCEN_NO, SCEN_NAME, PACK_NAME, SSS.STEP_NAME
FROM SNP_SCEN SC,
SNP_SCEN_STEP SSS,
SNP_PACKAGE P
WHERE SC.I_PACKAGE IS NOT NULL
AND SC.SCEN_NAME NOT LIKE '%99%'
AND SC.SCEN_NO = SSS.SCEN_NO
AND STEP_TYPE = 'F'
AND P.I_PACKAGE(+) = SC.I_PACKAGE
GROUP BY SCEN_NAME, PACK_NAME,SSS.STEP_NAME
) C
WHERE B."프로시져/인터페이스(작업흐름)" = TRIM(C.STEP_NAME(+))
ORDER BY 9,3,1