select
xrh.report_name,
xrh.version,
xxen_util.user_name(xrh.min_created_by) created_by,
xxen_util.client_time(xrh.min_creation_date) creation_date,
xxen_util.user_name(xrh.created_by) last_updated_by,
xxen_util.client_time(xrh.creation_date) last_update_date,
xrh.sql_text,
xrh.guid
from
(
select
count(*) over (partition by xrh.report_id) version,
max(xrh.creation_date) over (partition by xrh.report_id) max_creation_date,
min(xrh.creation_date) over (partition by xrh.report_id) min_creation_date,
min(xrh.created_by) keep (dense_rank first order by xrh.creation_date) over (partition by xrh.report_id) min_created_by,
xrh.*
from
xxen_reports_h xrh
) xrh
where
xrh.creation_date=xrh.max_creation_date and
not exists (select null from xxen_reports xr where xrh.report_id=xr.report_id)
order by
xrh.creation_date desc |
select
xrh.report_name,
xrh.version,
xxen_util.user_name(xrh.min_created_by) created_by,
xxen_util.client_time(xrh.min_creation_date) creation_date,
xxen_util.user_name(xrh.created_by) last_updated_by,
xxen_util.client_time(xrh.creation_date) last_update_date,
xrh.sql_text,
xrh.guid
from
(
select
count(*) over (partition by xrh.report_id) version,
max(xrh.creation_date) over (partition by xrh.report_id) max_creation_date,
min(xrh.creation_date) over (partition by xrh.report_id) min_creation_date,
min(xrh.created_by) keep (dense_rank first order by xrh.creation_date) over (partition by xrh.report_id) min_created_by,
xrh.*
from
xxen_reports_h xrh
) xrh
where
xrh.creation_date=xrh.max_creation_date and
not exists (select null from xxen_reports xr where xrh.report_id=xr.report_id)
order by
xrh.creation_date desc |
