ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Interface Mapping Query

This query will show the interface  mapping  in the following order

PROJECT_NAME  , FOLDER_NAME  ,  INTERFACE_NAME  , INTERFACE_TYPE , TARGET_SCHEMA , TARGET_TABLE , TARGET_COLUMN , TARGET_DATATYPE , SOURCE_SCHEMA , SOURCE_TABLE , SOURCE_COLUMN , SOURCE_DATATYPE , MAPPING  filtered by Interface , Folder  or  Project Name. I have commented out other option of Interface & Folder..

SELECT DISTINCT
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 'TABLE_TO_TABLE_INF'
ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE,
SNP_POP.LSCHEMA_NAME        AS TARGET_SCHEMA,
SNP_POP.TABLE_NAME          AS TARGET_TABLE,
SNP_POP_COL.COL_NAME        AS TARGET_COLUMN,
CASE
WHEN SNP_POP.WSTAGE='E' THEN T_COL.SOURCE_DT||' ('||T_COL.LONGC||')'
ELSE SNP_POP_COL.SOURCE_DT||' ('||SNP_POP_COL.LONGC||')'
END AS TRG_DATATYPE,
SNP_SOURCE_TAB.LSCHEMA_NAME AS SOURCE_SCHEMA,
SNP_TABLE.TABLE_NAME        AS SOURCE_TABLE,
SNP_COL.COL_NAME            AS SOURCE_COLUMN ,
SNP_COL.SOURCE_DT||' ('||SNP_COL.LONGC||')' AS SRC_DATATYPE,
rowtocol('SELECT DISTINCT TXT FROM SNP_TXT WHERE I_TXT='||i_txt ) AS COLUMN_MAPPING
-- to use without rowtocol use SNP_TXT.TXT AS COLUMN_MAPPING
FROM SNP_PROJECT
LEFT OUTER JOIN SNP_FOLDER ON SNP_FOLDER.I_PROJECT=SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN SNP_POP ON SNP_POP.I_FOLDER=SNP_FOLDER.I_FOLDER
LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_TXT S_TXT ON S_TXT.I_TXT=SNP_POP_CLAUSE.I_TXT_SQL
LEFT OUTER JOIN SNP_TXT ON SNP_TXT.I_TXT= SNP_POP_COL.I_TXT_MAP
LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_POP=SNP_POP.I_POP
LEFT OUTER JOIN SNP_TXT_CROSSR ON SNP_TXT_CROSSR.I_TXT=SNP_TXT.I_TXT
LEFT OUTER JOIN SNP_COL ON SNP_COL.I_COL=SNP_TXT_CROSSR.I_COL
LEFT OUTER JOIN SNP_TABLE ON SNP_TABLE.I_TABLE= SNP_COL.I_TABLE
LEFT OUTER JOIN SNP_COL T_COL ON T_COL.I_COL=SNP_POP_COL.I_COL
WHERE
-- SNP_POP.POP_NAME='&TAB'
-- SNP_FOLDER.FOLDER_NAME ='&TAB'
SNP_PROJECT.PROJECT_NAME='DEVELOPMENT'
ORDER BY SNP_POP.POP_NAME

I have used the rowtocol function  for SNP_TXT.TXT  ,as when  mapping length  is more than  256 character ODI stores in separate line with different i_txt number causing  mapping to come into two or multiple rows . In order to avoid such scenario and in order to get all the Mapping field information into a single column , i have used this function rowtocol.

Source code for  rowtocol function is available here – > http://www.oracle.com/technology/oramag/code/tips2004/050304.html 

image

11 Comments

  1. Hi all.

    How are you Cezar. All good mate!

    Well. I needed this SQL for some reason. Got your initial query Cezar and did some changes to work with ODI 11g 1.1.7.1

    Here follows:
    select distinct
    SNP_PROJECT.I_PROJECT,
    SNP_PROJECT.PROJECT_CODE as PROJECT_CODE,
    SNP_PROJECT.PROJECT_NAME as PROJECT_NAME,
    SNP_FOLDER.I_FOLDER,
    SNP_FOLDER.FOLDER_NAME as FOLDER_NAME,
    SNP_POP.I_POP,
    SNP_POP.POP_NAME AS INTERFACE_NAME,
    CASE
    WHEN SNP_POP.WSTAGE=’E’ THEN ‘TABLE_TO_TABLE_INF’
    ELSE ‘TEMP_INTERFACE’ END AS INTERFACE_TYPE,
    SNP_POP.LSCHEMA_NAME AS TARGET_SCHEMA,
    SNP_POP.TABLE_NAME AS TARGET_TABLE,
    SNP_POP_COL.COL_NAME as TARGET_COLUMN,
    SNP_POP_COL.POS,
    CASE
    WHEN SNP_POP.WSTAGE=’E’ THEN T_COL.SOURCE_DT||’ (‘||T_COL.LONGC||’)’
    ELSE SNP_POP_COL.SOURCE_DT||’ (‘||SNP_POP_COL.LONGC||’)’
    end as TRG_DATATYPE,
    T9.DS_NAME,
    T9.DS_ORDER,
    T9.DS_OPERATOR,
    T10.LSCHEMA_NAME AS SOURCE_SCHEMA,
    SNP_TABLE.TABLE_NAME AS SOURCE_TABLE,
    SNP_COL.COL_NAME AS SOURCE_COLUMN ,
    SNP_COL.SOURCE_DT||’ (‘||SNP_COL.LONGC||’)’ as SRC_DATATYPE
    –,BIM.rowtocol(‘SELECT DISTINCT TXT FROM SNP_TXT T1 WHERE T1.I_TXT=’||S_TXT.i_txt ) AS COLUMN_MAPPING
    — to use without rowtocol use SNP_TXT.TXT AS COLUMN_MAPPING
    ,SNP_TXT_CROSSR.STRING_ELT AS COLUMN_MAPPING
    FROM SNP_PROJECT
    left outer join SNP_FOLDER on SNP_FOLDER.I_PROJECT=SNP_PROJECT.I_PROJECT
    LEFT OUTER JOIN SNP_POP ON SNP_POP.I_FOLDER=SNP_FOLDER.I_FOLDER
    left outer join SNP_POP_COL on SNP_POP_COL.I_POP=SNP_POP.I_POP
    left outer join SNP_POP_CLAUSE on SNP_POP_CLAUSE.I_POP=SNP_POP.I_POP
    left outer join SNP_POP_MAPPING SNP_POP_MAP on SNP_POP_MAP.I_POP_COL=SNP_POP_COL.I_POP_COL
    left outer join SNP_SOURCE_TAB on SNP_SOURCE_TAB.I_POP=SNP_POP.I_POP
    left outer join SNP_TXT_CROSSR on SNP_TXT_CROSSR.I_TXT=SNP_POP_MAP.I_TXT_MAP
    left outer join SNP_COL on SNP_COL.I_COL=SNP_TXT_CROSSR.I_COL
    left outer join SNP_TABLE on SNP_TABLE.I_TABLE= SNP_COL.I_TABLE
    left outer join SNP_COL T_COL on T_COL.I_COL=SNP_POP_COL.I_COL
    left outer join SNP_DATA_SET T9 on T9.I_POP = SNP_POP.I_POP
    and T9.I_DATA_SET = SNP_POP_MAP.I_DATA_SET
    LEFT OUTER JOIN SNP_MODEL T10 ON T10.I_MOD = SNP_TABLE.I_MOD
    where 1=1
    –and snp_pop.pop_name = ‘Interface Name’
    order by SNP_POP.POP_NAME
    ,T9.DS_ORDER
    ,SNP_POP_COL.POS;

    Cheers.

    Fernando Ponte

  2. Can you publish same sql for ODI 12c repository?

  3. Hi,
    This is excellent work done by odi experts.
    But i want source table and column which is mapping to that column in a particular interface.
    and filter condition,join condition,lookups

  4. Hello Cezar Santos,

    Where you able to rewrite this query for ODI 11.1.1.7.1 ? If so can you please share it. Thanks in advance

  5. Hi Kshitiz

    I am required to do a similar thing for 12c,since the repo tables have a changed structure now . I am unable to establish a relation between a source col and target col . Pls help

  6. Hi
    It’s not working anymore with ODI 11.1.1.1.7. Source information is always empty.
    Regards

  7. Hi,
    I want to know which tables get populated when i create lookup interface in ODI 11g..

  8. Please Update the Code ….. I dont think it will work anymore

  9. Hi Kshitiz,

    Why you are not using ODI Metadata Navigator to get the interface mapping easily? In that case we don’t need to write such a big and complex query.

    Regards,
    Hasi…

    • Well , i got a request from someone to design such a query and moreover once development is done , i can use the same query and update my ETL mapping specs too ,which might be useful to some people .
      Moreover not all environment have Metadata Navigator Installed.

Leave a Reply

Required fields are marked *.