Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
How To identify the interfaces that are using a particular mapping value?

1009817
Member Posts: 6
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.
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.
Answers
-
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 -
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%' ; -
Hi,
try this query (really ugly but it was fast to write)Select * from SNP_POPi 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
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%')
)Select * from SNP_POPbut you have to test (i'm not sure if it was I_TXT_MAP or I_TXT_MAP_TXT)
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%')
)
Let us know -
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.