본문 바로가기

ETL Tools/Oracle Data Integrator

[ODI Repository] Interface Mapping 가져오기

/*

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

;