Forum Stats

  • 3,827,561 Users
  • 2,260,792 Discussions
  • 7,897,297 Comments

Discussions

How To identify the interfaces that are using a particular mapping value?

1009817
1009817 Member Posts: 6
edited May 24, 2013 3:17AM in Data Integrator
I need to replace a hard coded mapping expression value with a ODI variable. Now the question is . how to identify as to how many interfaces are actually using that hard coded value in its mapping expression ?
The only way I right now know is manually checking all the interfaces in their "Quick Edit" Mode and if the value is mapped I would be replacing it with that variable.

Is there any way like performing a search for all interfaces that are using the hard coded value and then I can replace it with the variable in those interfaces.
Tagged:

Answers

  • Bos
    Bos Member Posts: 338 Bronze Badge
    Hi,

    Have you got access to the ODI repository schema? there is a table SNP_TXT_CROSSR where it holds the mapping information. The column STRING_ETL contains this - you can query this table to see how often it is used - it has got the I_COL column which relates to the specific column in the mapping so you can join that from SNP_COL to SNP_TABLE to find the table. from there, you should be able to find the interfaces by checking where it is used on Target from the ODI Model definition.

    Cheers

    Bos
    Bos
  • 1009817
    1009817 Member Posts: 6
    Hi,

    Thank you for your response. But the value for I_COL is being populated as null in my query. Although I get some 30 records as output but all of them are showing NULL value for the mentioned column.
    Is there any other way to get that ? The query I used is -

    select * from SNP_TXT_CROSSR where STRING_ELT LIKE '%12-31%' ;
  • DecaXD
    DecaXD Member Posts: 742
    Hi,

    try this query (really ugly but it was fast to write)

    Select * from SNP_POP
    where I_POP in
    (
    Select I_POP from SNP_POP_COL
    where i_pop_col in
    ( select I_POP_COL from SNP_TXT_CROSSR where STRING_ELT LIKE '%12-31%')
    )
    i think there was a typo in Bos reply. Also give a look (but i haven't checked) at SNP_EXT_TXT. if your hardcoded is under 250 char you could try
    Select * from SNP_POP
    where I_POP in
    (
    Select I_POP from SNP_POP_COL
    where i_txt_map in
    ( select I_TXT from SNP_EXT_TXT where TXT LIKE '%12-31%')
    )
    but you have to test (i'm not sure if it was I_TXT_MAP or I_TXT_MAP_TXT)

    Let us know
    DecaXD
  • 1009817
    1009817 Member Posts: 6
    edited May 24, 2013 3:17AM
    Hi,

    i modified a lil and the analysis worked out..

    the Final query i framed is ---


    SELECT SP.POP_NAME, SPC.COL_NAME, spc.I_TXT_MAP, stc.i_txt FROM SNP_POP SP, SNP_POP_COL SPC, SNP_TXT_CROSSR stc
    WHERE SP.I_POP = SPC.I_POP AND
    SPC.I_TXT_MAP = STC. I_TXT
    AND STC.STRING_ELT LIKE '%12-31%';

    And it would be SNP_POP_COL.I_TXT_MAP


    However, only with query STRING_ELT LIKE '%12-31%' , I get some 30 records as I stated before .. but after framing the final query ,
    I jus observed 11 records.

    there are afew I_TXT values of SNP_TXT_CROSSR that do not map with I_TXT_MAP column of SNP_POP_COL . so those are getting missed. I tried other combinations but there is no way I found out to get the missing I_TXT_MAP values too.
    SO, as against 35 records I have 11 as output but apart from these 11 interfaces there are other interfaces that I know of that contain matching string..

    So a lil disarray but still
    Thank You both of you for your responses. !! :)

    Ill try to seek if anything works out.

    Edited by: 1006814 on May 24, 2013 12:12 AM
This discussion has been closed.