Thursday, June 10, 2010

Repository tables Expression query

In a mapping with multiple expression transformations and multiple unconnected lookups, it is difficult for you to identify the expressions calling these unconnected lookups. This SQL will give you what you are looking for.



select w.instance_name,
g.expression
from opb_widget_expr f,
opb_expression g,
opb_widget_inst w,
opb_mapping m
where f.expr_id = g.expr_id
and f.widget_id = w.widget_id
and w.widget_type = 5
and w.mapping_id = m.mapping_id
and m.mapping_name = 'm_Open_Trades_Target_load'

8 comments:

Anonymous said...

It is very useful.. To add something more following query can used to know the transformations used in the mapping by giving the folder and the mapping name

select wid.widget_name from opb_widget wid,opb_subject sub,opb_mapping mapp
where wid.subject_id = sub.subj_id and
wid.ru_parent_id = mapp.mapping_id and
mapp.
and sub.subj_name = 'Folder name'
and mapp.mapping_name = 'Mapping Name'

Ajay said...

Very useful query..
I have a question why all dates in information repository tables are stored and varchar?

ramgokul said...

This is good artitcle.
Can you please send the Informatica 8.6 architecture


Email : sriramgokul6666@gmail.com

Anonymous said...

Can you please send me Informatica architecture to sudheer141@gmail.com

srivignesh.kn said...

Hi Sarma,

Your blog is very useful. Am new to informatica, Can you please tell from where can we query the repository tables. I mean is there any specific database where these query can be run.

Regards,
Vignesh

Sri said...

Hi Sarma,
Your blog is quite unique. I check your blog almost once a week if there any new updates.But..unfortunately no.Please share your work experiences about Infa,UNix,Oracle and let us gain more knowledge. I suppose you are quite busy with your work.But please allocate an hour a month atleast to write for your blog.

Thanks,
Sri

Anonymous said...

Radha,

I need to find the sessions that use the Success/Failure Email tasks. Could you suggest any queries to find it from Repository tables?

Sri

Daisy Dreamz said...

Thanks for the post, For online training on informatica visit Tekslate.

 
HTML Hit Counters