Wednesday, August 5, 2015

How to get source tables and target table to an interface from backend in ODI?


Steps:
1. Login to Work Repository Schema

2.Run below Query:

SELECT distinct SRC_COL.COL_NAME AS "SRC_COLUMN", 
TRG_COL.COL_NAME AS "TRG_COLUMN",
SRC_TABLE.TABLE_NAME AS "SRC_TABLE",
TRG_TABLE.TABLE_NAME AS "TRG_TABLE", 
TRG_TABLE.POP_NAME AS "INTERFACE_NAME",
CASE
WHEN TRG_TABLE.WSTAGE='E' THEN 'TABLE_TO_TABLE_INF'
ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE
FROM SNP_POP_MAPPING COL_MAP,
SNP_TXT_CROSSR SRC_COL_ID,
SNP_POP_COL TRG_COL,
SNP_COL SRC_COL,
SNP_TABLE SRC_TABLE,
SNP_POP TRG_TABLE
WHERE (1=1)
AND COL_MAP.I_TXT_MAP = SRC_COL_ID.I_TXT
AND TRG_COL.I_POP_COL = COL_MAP.I_POP_COL
AND SRC_COL_ID.I_COL = SRC_COL.I_COL
AND SRC_COL.I_TABLE = SRC_TABLE.I_TABLE
AND TRG_COL.I_POP = TRG_TABLE.I_POP;


5 comments:

  1. where to write this query in odi or somewhere

    ReplyDelete
    Replies
    1. Hi Mounika,

      You need to connect work repository schema there you need to fire this query. So that you will get source and target table names.

      Thanks,
      Trinesh Koduru

      Delete
  2. hw about the query which gives the same ResultSet in ODI 12c

    ReplyDelete
  3. This is not working for ODI 12c. As all the SNP_POP table sare not used in ODI 12c.

    ReplyDelete