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