-- Using ODI Repository Info by SQL Query
-- ODI Version: ODI 10g 10.1.3.5.1
-- Created by Cheon Jeonghyeon
-- Created on 2011.11.18
-- Created on 2011.11.18
-- WideTNS Co., Ltd
-- Interface Job Information
-- Project Name
-- Folder Path in Project
-- Interface Name
-- Source Table Name
-- Target Table Name
SELECT
P.I_POP I_POP,
PROJECT_NAME,
FOLDER_PATH,
POP_NAME,
S.LSCHEMA_NAME||'.'||S.TABLE_NAME SRC_TAB,
P.LSCHEMA_NAME||'.'||P.TABLE_NAME SRC_TAB
FROM SNP_POP P,
SNP_SOURCE_TAB S,
(
SELECT I_FOLDER,
PROJECT_NAME,
SYS_CONNECT_BY_PATH(FOLDER_NAME, '/') FOLDER_PATH
FROM SNP_FOLDER F,
SNP_PROJECT P
WHERE 1=1
AND F.I_PROJECT = P.I_PROJECT
START WITH PAR_I_FOLDER IS NULL
CONNECT BY PRIOR I_FOLDER = PAR_I_FOLDER ) F
WHERE 1=1
AND P.I_POP = S.I_POP
AND P.I_FOLDER = F.I_FOLDER
;
-- FOLDER PATH 가져오기
SELECT I_FOLDER,
PROJECT_NAME,
SYS_CONNECT_BY_PATH(FOLDER_NAME, '/')
FROM SNP_FOLDER F, SNP_PROJECT P
WHERE 1=1
AND F.I_PROJECT = P.I_PROJECT
START WITH PAR_I_FOLDER IS NULL
CONNECT BY PRIOR I_FOLDER = PAR_I_FOLDER
;
-- TARGET TABLE COLUMN'S INFO
SELECT
P.I_POP I_POP,
PROJECT_NAME,
FOLDER_PATH,
POP_NAME,
P.LSCHEMA_NAME||'.'||P.TABLE_NAME TRG_TAB
FROM SNP_POP P, -- INTERFACE
(
SELECT I_FOLDER,
PROJECT_NAME,
SYS_CONNECT_BY_PATH(FOLDER_NAME, '/') FOLDER_PATH
FROM SNP_FOLDER F,
SNP_PROJECT P
WHERE 1=1
AND F.I_PROJECT = P.I_PROJECT
START WITH PAR_I_FOLDER IS NULL
CONNECT BY PRIOR I_FOLDER = PAR_I_FOLDER ) F, -- FOLDER
SNP_POP_COL C -- TABLE
WHERE 1=1
AND P.I_FOLDER = F.I_FOLDER
AND P.I_TABLE = T.I_TABLE
;
-- INTERFACE COLMN MAPPING
SELECT POP_NAME,COL_NAME, TXT COL_MAP
FROM SNP_POP_COL C,
SNP_POP P, SNP_TXT T
WHERE 1=1
AND P.I_POP = C.I_POP
AND P.I_POP = 624000
AND P.I_POP = C.I_POP
AND C.I_TXT_MAP = T.I_TXT
;
-- INTERFACE FILTERING INFO
SELECT POP_NAME,
NVL2(I_TABLE2, DECODE(IND_OUTER1, 1, 'LEFT ')||DECODE(IND_OUTER2, 1, 'RIGHT ')||DECODE(IND_OUTER1+IND_OUTER2, 0, 'INNER ', 'OUTER ')||'JOIN', 'FILTER') CLAUSE_TYPE,
TXT CLAUSE_STATEMENT,
I_TABLE1,
I_TABLE2
FROM SNP_POP_CLAUSE PC,
SNP_POP P,
SNP_TXT T
WHERE 1=1
AND P.I_POP = PC.I_POP
AND I_TXT = I_TXT_SQL;
-- create SQL Query WITHOUT GROUP BY AND HAVING STATEMENTS
-- create SQL Query WITHOUT GROUP BY AND HAVING STATEMENTS
-- ODI Repository Version ODI 10g 10.1.3.5.1
-- Created by Cheon Jeonghyeon
-- WideTNS Co., Ltd
-- 2011.11.22
SELECT -- Description
'-- Job Path: '||PROJECT_NAME|| ' - ' ||
FOLDER_PATH|| ' Interface Name: ' ||
POP_NAME|| ' Source Table: ' ||
S.LSCHEMA_NAME||'.'||S.TABLE_NAME || ' Target Table: ' ||
P.LSCHEMA_NAME||'.'||P.TABLE_NAME SQL_Query
FROM SNP_POP P,
SNP_SOURCE_TAB S,
(
SELECT I_FOLDER,
PROJECT_NAME,
SYS_CONNECT_BY_PATH(FOLDER_NAME, '/') FOLDER_PATH
FROM SNP_FOLDER F,
SNP_PROJECT P
WHERE 1=1
AND F.I_PROJECT = P.I_PROJECT
START WITH PAR_I_FOLDER IS NULL
CONNECT BY PRIOR I_FOLDER = PAR_I_FOLDER ) F
WHERE 1=1
AND P.I_POP = S.I_POP
AND P.I_FOLDER = F.I_FOLDER
AND P.I_POP = 593000
UNION ALL
SELECT -- Insert into
DISTINCT 'INSERT INTO '|| TABLE_NAME || '('
FROM SNP_POP
WHERE I_POP = 593000
UNION ALL
SELECT -- TARGET TABLE COLUMN STATEMENTS
COL_NAME|| NVL2(LEAD(COL_NAME) OVER(ORDER BY I_COL),', ','')
FROM SNP_POP P,
SNP_POP_COL T
WHERE 1=1
AND P.I_POP = T.I_POP
AND P.I_POP = 593000
UNION ALL
SELECT ') SELECT ' FROM DUAL
UNION ALL
SELECT -- MAPPING STATEMENTS
TXT || NVL2( LEAD(TXT) OVER(ORDER BY I_COL) ,', ','')
FROM SNP_POP_COL C,
SNP_POP P,
SNP_TXT T
WHERE 1=1
AND P.I_POP = C.I_POP
AND P.I_POP = C.I_POP
AND C.I_TXT_MAP = T.I_TXT
AND P.I_POP = 593000
UNION ALL
SELECT 'FROM' FROM DUAL
UNION ALL
SELECT -- SOURCE TABLES
S.LSCHEMA_NAME||'.'||S.TABLE_NAME || NVL2( LEAD(S.TABLE_NAME) OVER(ORDER BY S.TABLE_NAME) ,', ','')
FROM SNP_POP P,
SNP_SOURCE_TAB S
WHERE 1=1
AND P.I_POP = S.I_POP
AND P.I_POP = 593000
UNION ALL
SELECT 'WHERE (1 = 1)' FROM DUAL
UNION ALL
SELECT -- WHERE STATEMENTS
'AND '||TXT CLAUSE_STATEMENT
FROM SNP_POP_CLAUSE PC,
SNP_POP P,
SNP_TXT T
WHERE 1=1
AND P.I_POP = PC.I_POP
AND P.I_POP = 593000
AND I_TXT = I_TXT_SQL
