Tuesday, June 15, 2010

SQL Overrides in Lookups, Source Qualifier etc

I have seen in many instances, you would want to know the SQL overrides written in a mapping Source Qualifier transformation or Lookup SQL override or Pre-SQL & Post-SQL etc. Below is the query that takes the workflow name & Folder name as input and gives you all the SQL overrides wherever they are in the workflow.

select folder, wf_name, 
sess_name, mapping_name,
transformation_name, attr_name,
line_no, sql_value
from (select f.subj_name folder,
wf.task_name wf_name,
sess.instance_name sess_name,
m.mapping_name mapping_name,
w_inst.instance_name transformation_name,
attr.line_no, attr.attr_value sql_value,
attr_type.attr_name attr_name,
row_number() over (partition by wf.task_name,
sess.instance_name,
m.mapping_name,
w_inst.instance_name,
attr.line_no,
attr.attr_value
order by attr.session_task_id desc
) rn
from opb_task_inst wf_inst
,opb_task_inst sess
,opb_session s
,opb_mapping m
,opb_subject f
,opb_widget_attr attr
,opb_widget_inst w_inst
,opb_task wf
,(select o.object_type_id object_type_id,
o_attr.attr_id attr_id,
o.object_type_name||': '||o_attr.attr_name attr_name
from opb_attr o_attr,
opb_object_type o
where o.object_type_id = o_attr.object_type_id
and o_attr.attr_datatype = 2
and o_attr.attr_value is null
and upper(o_attr.attr_name) like '%SQL%'
) attr_type
where wf_inst.task_id = sess.task_id
and sess.task_type = 68
and sess.task_id = s.session_id
and wf.subject_id = f.subj_id
and s.mapping_id = m.mapping_id
and attr.widget_id = w_inst.widget_id
and w_inst.mapping_id = m.mapping_id
and w_inst.widget_type = attr_type.object_type_id
and wf_inst.workflow_id = wf.task_id
and wf.task_type = 71
and (attr.session_task_id = s.session_id
or attr.session_task_id = 0)
and attr.attr_id = attr_type.attr_id
and attr.attr_value is not null
and attr.attr_value <> '0'
and wf.task_name = 'WORKFLOW_NAME'
and f.subj_name = 'FOLDER_NAME'
)
where rn = 1
order by 1, 2, 3, 4, 5, 6, 7

Shortcut Object and its parent folder

Have you always wondered how to get the shortcut object & it's parent folder. This requirement is imminent in folders where you have tens of shortcut transformations & mappings. Below query can get you what you want.

select shortcut_name, 
case s.object_type
when 21 then 'Mapping'
when 23 then (select o.object_type_name
from opb_object_type o
,opb_widget w
where w.widget_id = s.object_id
and w.widget_type = o.object_type
)
when 24 then 'Target'
when 25 then 'Source'
when 44 then 'Mapplet'
else 'Nothing'
end Object_Type,
decode(s.object_type, 21, (select f.subj_name
from opb_subject f,
opb_mapping m
where m.mapping_id = s.object_id
and m.subject_id = f.subj_id
),
23, (select f.subj_name
from opb_subject f,
opb_widget w
where w.widget_id = s.object_id
and w.subject_id = f.subj_id
),
25, (select f.subj_name
from opb_subject f,
opb_src src
where src.src_id = s.object_id
and src.subj_id = f.subj_id
),
24, (select f.subj_name
from opb_subject f,
opb_targ t
where t.target_id = s.object_id
and t.subj_id = f.subj_id
),
44, (select f.subj_name
from opb_subject f,
opb_widget w
where w.widget_id = s.object_id
and w.subject_id = f.subj_id
)
) obj_original_folder
from opb_shortcut s,
opb_subject f
where s.subject_id = f.subj_id
and f.subj_name = 'FOLDER_NAME'

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'
 
HTML Hit Counters