select distinct
x.report_name,
x.column_label,
x.function_name,
x.calculation_column
from
(
select
rr.report_name,
rrc.column_label,
lower(regexp_substr(rrc.calculation_column,'((\w+\.)?\w+)\s*\(',1,rowgen.column_value,null,1)) function_name,
rrc.calculation_column
from
rx_reports rr,
rx_report_columns rrc,
table(xxen_util.rowgen(regexp_count(rrc.calculation_column,'(\w+\.)?\w+\s*\('))) rowgen
where
rr.report_id=rrc.report_id and
rrc.view_column_id=-1 and
rrc.enabled_flag='Y' and
rrc.report_id in (select rrs.report_id from rx_report_stats rrs where rrs.start_time>sysdate-:history_days)
) x
where
x.function_name not in (
'concat',
'next_day',
'and',
'or',
'in',
'else',
'when',
'count',
'sum',
'avg',
'min',
'max',
'abs',
'add_months',
'bitand',
'cast',
'ceil',
'chr',
'coalesce',
'decode',
'greatest',
'initcap',
'instr',
'last_day',
'least',
'length',
'lower',
'lpad',
'mod',
'months_between',
'nvl',
'nvl2',
'power',
'regexp_replace',
'regexp_substr',
'replace',
'round',
'rtrim',
'sign',
'substr',
'substrb',
'to_char',
'to_date',
'to_number',
'translate',
'trim',
'trunc',
'upper',
'userenv'
)
order by
x.report_name,
x.column_label,
x.function_name |
