/*
Created by Cheon.jh from WideTNS. Co, Ltd.
Created on 2012.06.20
Using on Comparing with the Mapping Definition Document
Search mapping expressions on ODI Interface.
Version 1.1
Added: All column Mapping(including Target Area Mapping)
*/
WITH A AS (
SELECT
P.POP_NAME,
P.TABLE_NAME,
DS.DS_ORDER||'('||DS_NAME||')' DATASET,
PC.POS COL_ORD,
PC.I_POP_COL,
PC.COL_NAME,
DS.I_DATA_SET,
PC.I_TXT_MAP
FROM
SNP_POP P,
SNP_POP_COL PC,
SNP_DATA_SET DS
WHERE 1=1
AND P.TABLE_NAME LIKE 'Table 명'
AND P.POP_NAME = 'Interface 명'
AND P.I_POP = PC.I_POP
AND P.I_POP = DS.I_POP
)
SELECT
A.POP_NAME,
A.TABLE_NAME,
A.DATASET,
A.COL_ORD,
A.COL_NAME,
DBMS_LOB.SUBSTR( TH.FULL_TEXT, 4000, 1) MAP_TEXT
FROM A,
SNP_POP_MAPPING PM,
SNP_TXT_HEADER TH
WHERE 1=1
AND A.I_POP_COL = PM.I_POP_COL(+)
AND PM.I_DATA_SET(+) = A.I_DATA_SET
AND TH.I_TXT = NVL(A.I_TXT_MAP ,PM.I_TXT_MAP)
ORDER BY
POP_NAME,
DATASET,
COL_ORD
;
/*
Created by Cheon.jh from WideTNS. Co, Ltd.
Created on 2012.05.25
Using on Comparing with the Mapping Definition Document
Search mapping expressions on ODI Interface.
*/
SELECT
POP_NAME,
TABLE_NAME,
DS.DS_ORDER||'('||DS_NAME||')' DATASET,
PC.POS COL_ORD,
COL_NAME,
DBMS_LOB.SUBSTR( FULL_TEXT, 4000, 1) MAP_TEXT
FROM
SNP_POP P,
SNP_POP_COL PC,
SNP_POP_MAPPING PM,
SNP_DATA_SET DS,
SNP_TXT_HEADER TH
WHERE 1=1
--AND POP_NAME = ''
AND P.I_POP = PC.I_POP
AND PC.I_POP_COL = PM.I_POP_COL
AND PM.I_DATA_SET = DS.I_DATA_SET
AND P.I_POP = DS.I_POP
AND TH.I_TXT = PM.I_TXT_MAP
ORDER BY
P.I_POP,
DS.DS_ORDER,
PC.POS
;