Finding all mappings that use a table as source, target and lookups

来源：互联网发布：java多线程项目源码编辑：程序博客网时间：2024/05/18 00:58

If you are using version 7.x, this query will get you list of all the mappings that use the table as source, target and lookup. You need to change the 'FOLDER_NAME' filter value, or remove it.

select SUBJ_NAME, MAPPING_NAME, TYPE from (
--table in source
select OPB_SUBJECT.SUBJ_NAME, OPB_MAPPING.MAPPING_NAME , 'SOURCE' AS TYPE
from opb_mapping, opb_subject, opb_src, opb_widget_inst
where opb_subject.SUBJ_ID = opb_mapping.SUBJECT_ID
and OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
and OPB_WIDGET_Inst.WIDGET_ID = OPB_SRC.SRC_ID
and upper(OPB_SRC.source_name) like '%'||upper(:TABLENAME)||'%'
and OPB_widget_inst.widget_type=1
AND OPB_SUBJECT.SUBJ_NAME = 'FOLDER_NAME'
UNION
--table in target
select OPB_SUBJECT.SUBJ_NAME, OPB_MAPPING.MAPPING_NAME, 'TARGET' AS TYPE
from opb_mapping, opb_subject, opb_TARG, opb_widget_inst
where opb_subject.SUBJ_ID = opb_mapping.SUBJECT_ID
and OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
and OPB_WIDGET_Inst.WIDGET_ID = OPB_TARG.TARGET_ID
and upper(OPB_TARG.TARGET_NAME) like '%'||upper(:TABLENAME)||'%'
and OPB_widget_inst.widget_type=2
AND OPB_SUBJECT.SUBJ_NAME = 'FOLDER_NAME'
union
--table in UD_Lkp of UD_SRC
select OPB_SUBJECT.SUBJ_NAME, OPB_MAPPING.MAPPING_NAME, 'LKP1' AS TYPE
from opb_mapping, opb_subject, opb_WIDGET, opb_widget_inst, OPB_widget_attr
where opb_subject.SUBJ_ID = opb_mapping.SUBJECT_ID
and OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
AND opb_widget_inst.WIDGET_TYPE in (3, 11)
and opb_widget_attr.WIDGET_TYPE in (3, 11)
and OPB_WIDGET_INST.WIDGET_ID = OPB_WIDGET_ATTR.WIDGET_ID
and OPB_WIDGET_INST.WIDGET_TYPE = OPB_WIDGET_ATTR.WIDGET_TYPE
and OPB_WIDGET_ATTR.ATTR_ID = 1
and upper(OPB_WIDGET_ATTR.ATTR_VALUE) like '%'||upper(:TABLENAME)||'%'
AND OPB_SUBJECT.SUBJ_NAME = 'FOLDER_NAME'
--table in Lkp
union
select OPB_SUBJECT.SUBJ_NAME, OPB_MAPPING.MAPPING_NAME, 'LKP2' AS TYPE
from opb_mapping, opb_subject, opb_WIDGET, opb_widget_inst, OPB_widget_attr
where opb_subject.SUBJ_ID = opb_mapping.SUBJECT_ID
and OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
AND opb_widget_inst.WIDGET_TYPE in (11)
and opb_widget_attr.WIDGET_TYPE in (11)
and OPB_WIDGET_INST.WIDGET_ID = OPB_WIDGET_ATTR.WIDGET_ID
and OPB_WIDGET_INST.WIDGET_TYPE = OPB_WIDGET_ATTR.WIDGET_TYPE
and OPB_WIDGET_ATTR.ATTR_ID = 2
and upper(OPB_WIDGET_ATTR.ATTR_VALUE) like '%'||upper(:TABLENAME)||'%'
AND OPB_SUBJECT.SUBJ_NAME = 'FOLDER_NAME'
)
order by SUBJ_NAME, mapping_name

0 0