select distinct
decode(count(distinct y.report_id) over (partition by y.view_ids),1,rr.report_name,'<multiple>') report_name,
y.views,
y.db_views,
count(*) over (partition by y.view_ids) execution_count,
count(distinct y.report_id) over (partition by y.view_ids) report_count,
count(distinct rrs.user_id) over (partition by y.view_ids) user_count,
y.view_ids
from
(
select distinct
x.report_id,
listagg(rv.view_alias,', ') within group (order by rv.view_alias) over (partition by x.report_id) views,
listagg(rv.db_view_name,', ') within group (order by rv.view_alias) over (partition by x.report_id) db_views,
listagg(x.view_id,',') within group (order by x.view_id) over (partition by x.report_id) view_ids
from
(
select distinct
rrc.report_id,
rvc.view_id
from
rx_report_columns rrc,
rx_view_columns rvc
where
rrc.view_column_id=rvc.view_column_id
) x,
rx_views rv
where
2=2 and
x.view_id=rv.view_id
) y,
rx_report_stats rrs,
rx_reports rr
where
y.report_id=rrs.report_id and
y.report_id=rr.report_id and
1=1
order by
execution_count desc,
y.views |
