/
excel_only_reportbooks.sql
92 lines (85 loc) · 2.9 KB
/
excel_only_reportbooks.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
REM excel_only_reportbooks.sql
REM (c)Go-Faster Consultancy 2021
REM Reportbooks with layouts identified as required to be run on Excel nVision
break on report
compute sum of all_layouts on report
compute sum of num_excel_layouts on report
column oprid format a10
column runcntlid format a20
column all_layouts heading 'All|Layouts' format 9999
column num_excel_layouts heading 'Number of|Excel|Layouts'
column excel_layouts heading 'Excel Layouts' format a30
column openxml_layouts heading 'OpenXML Layouts' format a70
spool excel_only_reportbooks
set lines 180
ttitle 'ReportBooks with both Excel and OpenXML nVision layouts'
with x as (
select b.oprid, b.run_cntl_id
, COUNT(DISTINCT n.layout_id) all_layouts
, count(DISTINCT e.layout_id) num_excel_layouts
, listagg(DISTINCT e.layout_id,', ') within group (order by e.layout_id) excel_layouts
, listagg(DISTINCT CASE WHEN e.layout_id IS NULL THEN n.layout_id END,', ') within group (order by e.layout_id) openxml_layouts
FROM psnvsbookrequst b
, ps_nvs_report n
LEFT OUTER JOIN ps_nvs_redir_excel e
ON n.layout_id = e.layout_id
AND e.eff_status = 'A'
where b.eff_status = 'A'
and n.business_unit = b.business_unit
and n.report_id = b.report_id
group by b.oprid, b.run_cntl_id
)
select *
from x
where num_excel_layouts > 0
and all_layouts > num_excel_layouts
/
ttitle 'ReportBooks with only Excel nVision layouts'
with x as (
select b.oprid, b.run_cntl_id
, COUNT(DISTINCT n.layout_id) all_layouts
, count(DISTINCT e.layout_id) num_excel_layouts
, listagg(DISTINCT e.layout_id,', ') within group (order by e.layout_id) excel_layouts
, listagg(DISTINCT CASE WHEN e.layout_id IS NULL THEN n.layout_id END,', ') within group (order by e.layout_id) openxml_layouts
FROM psnvsbookrequst b
, ps_nvs_report n
LEFT OUTER JOIN ps_nvs_redir_excel e
ON n.layout_id = e.layout_id
AND e.eff_status = 'A'
where b.eff_status = 'A'
and n.business_unit = b.business_unit
and n.report_id = b.report_id
group by b.oprid, b.run_cntl_id
)
select *
from x
where num_excel_layouts > 0
and all_layouts = num_excel_layouts
ORDER BY 1,2
/
/*
ttitle 'ReportBooks with only OpenXML nVision layouts'
with x as (
select b.oprid, b.run_cntl_id
, COUNT(DISTINCT n.layout_id) all_layouts
, count(DISTINCT e.layout_id) num_excel_layouts
, listagg(DISTINCT e.layout_id,', ') within group (order by e.layout_id) excel_layouts
, listagg(DISTINCT CASE WHEN e.layout_id IS NULL THEN n.layout_id END,', ') within group (order by e.layout_id) openxml_layouts
FROM psnvsbookrequst b
, ps_nvs_report n
LEFT OUTER JOIN ps_nvs_redir_excel e
ON n.layout_id = e.layout_id
AND e.eff_status = 'A'
where b.eff_status = 'A'
and n.business_unit = b.business_unit
and n.report_id = b.report_id
group by b.oprid, b.run_cntl_id
)
select *
from x
where num_excel_layouts = 0
and all_layouts > 0
ORDER BY 1,2
*/
ttitle off
spool off