Saturday, July 11, 2015

Interface mapping Query ODI 11G


Below query will give you complete details of an ODI PROJECT like
PROJECT_NAME,FOLDER_NAME,INTERFACE_NAME,INTERFACE_TYPE,JOIN_CONDITION_OR_FILTER_TEXT, KM_USED,SOURCE_TABLES,COLUMNS and TARGET_TABLES,COLUMNS.
(Without connecting to ODI STUDIO to know the column mappings)

SELECT
SNP_PROJECT.PROJECT_NAME    AS PROJECT_NAME,
SNP_FOLDER.FOLDER_NAME      AS FOLDER_NAME,
SNP_POP.POP_NAME            AS INTERFACE_NAME,
CASE
WHEN SNP_POP.WSTAGE='E' THEN 'PRIMARY_INTEFACE'
ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE,
SNP_TXT_HEADER.FULL_TEXT JOIN_CONDITION_OR_FILTER_TEXT,
DECODE (SNP_POP_CLAUSE.JOIN_TYPE ,'J', 'JOIN','FILTER') EXPRESSION_TYPE,
NVL(STL.TRT_NAME,'NONE') AS LKM_USED ,
NVL(STI.TRT_NAME,'NONE') AS IKM_USED ,
NVL(STC.TRT_NAME,'NONE') AS CKM_USED,
SNP_SOURCE_TAB.SRC_TAB_ALIAS SOURCE_TABLE,
SNP_COL.COL_NAME  AS SOURCE_COLUMN,
SNP_POP.LSCHEMA_NAME        AS TARGET_SCHEMA,
SNP_POP.TABLE_NAME          AS TARGET_TABLE,
SNP_POP_COL.COL_NAME        AS TARGET_COLUMN
FROM SNP_FOLDER
LEFT OUTER JOIN SNP_PROJECT ON SNP_PROJECT.I_PROJECT=SNP_FOLDER.I_PROJECT
LEFT OUTER JOIN SNP_POP ON SNP_FOLDER.I_FOLDER = SNP_POP.I_FOLDER
LEFT OUTER JOIN SNP_DATA_SET ON SNP_DATA_SET.I_POP = SNP_POP.I_POP
LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP_CLAUSE = SNP_DATA_SET.I_POP
LEFT OUTER JOIN SNP_TXT_HEADER  ON SNP_POP_CLAUSE.I_TXT_SQL  = SNP_TXT_HEADER.I_TXT
LEFT OUTER JOIN SNP_SRC_SET ON SNP_POP.I_POP = SNP_SRC_SET.I_POP
LEFT OUTER JOIN SNP_TRT STL ON SNP_SRC_SET.I_TRT_KLM = STL.I_TRT
LEFT OUTER JOIN SNP_TRT STI ON STI.I_TRT = SNP_POP.I_TRT_KIM
LEFT OUTER JOIN SNP_TRT STC ON STC.I_TRT = SNP_POP.I_TRT_KCM
LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_DATA_SET=SNP_DATA_SET.I_DATA_SET
LEFT OUTER JOIN SNP_COL ON SNP_SOURCE_TAB.I_TABLE=SNP_COL.I_TABLE
LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP
WHERE
SNP_PROJECT.PROJECT_NAME='DEVELOPMENT'
--and SNP_TXT_HEADER.FULL_TEXT is not null
--AND SNP_SOURCE_TAB.I_TABLE=SNP_COL.I_TABLE

ORDER BY SNP_POP.POP_NAME;


K@run@

No comments:

Post a Comment