Discoverer: Find if a column is used in workbook

Categories: Other

This handy query will assist you in finding if a column name is already being used in any discoverer report/workbook

select * from
(
SELECT
o100588.doc_name workbook_name,
— o100588.doc_developer_key workbook_key,
decode(o100590.ex_to_type, ‘ITE’, ‘Item’, ‘JOI’, ‘Join’, ‘FIL’, ‘Condition’, o100590.ex_to_type) dependency_type,
o100590.ex_to_par_name folder,
(decode(SUBSTR(o100590.ex_to_name, 1, 19), ‘AUTO_GENERATED_NAME’, (decode(o100590.ex_to_type, ‘JOI’, (decode((decode(o100590.ex_to_type, ‘JOI’, EUL_US.EUL5_get_join_expid(o100590.ex_to_id, o100590.ex_to_devkey), to_number(NULL))), NULL, NULL, EUL_US.EUL5_get_join((decode(o100590.ex_to_type, ‘JOI’, EUL_US.EUL5_get_join_expid(o100590.ex_to_id, o100590.ex_to_devkey), to_number(NULL)))))), ‘ITE’, (decode((decode(o100590.ex_to_type, ‘ITE’, o100590.ex_to_id, to_number(NULL))), NULL, NULL, EUL_US.EUL5_get_autogen_item_name((decode(o100590.ex_to_type, ‘ITE’, o100590.ex_to_id, to_number(NULL)))))), ‘FIL’, (decode((decode(o100590.ex_to_type, ‘FIL’, o100590.ex_to_id, to_number(NULL))), NULL, NULL, o100590.ex_to_name)), NULL)), o100590.ex_to_name)) Dependency_name ,
o100590.ex_to_par_devkey folder_key,
(
select sobj_ext_table from EUL_US.EUL5_OBJS
where obj_developer_key=o100590.ex_to_par_devkey
) Table_View
, o100590.ex_to_devkey Dependency_key
FROM EUL_US.EUL5_documents o100588,
EUL_US.EUL5_elem_xrefs o100590
WHERE(((to_number(o100590.ex_from_id)) = o100588.doc_id))
AND(o100588.doc_content_type = ‘application/vnd.oracle-disco.wb’)
and ( o100588.doc_id = 0 or lower(o100588.doc_name) like lower(‘%’) )
)
where lower(dependency_name) like lower(‘%column_name%’)

« « Successful Business Intelligence Software Implementation                        Excel Dashboards – Simplified » »

Comments are closed.