将查询转换为视图

Question

我有一个很长的查询，根据句点计算几个itens。话虽如此，我有周期 P1(转换为 07：00：00 和 08：59：59，就这样。我必须按医生和时期计算所有计数.我使用绑定值在 :d t_inicial 和 :d t_final 之间搜索表(trunc(a.dt_agenda， 'dd') 由于我使用 UNION ALL 将所有句点放在一起，因此查询很长，我需要将其存储在 varchar2 字段 (4000) 中。 因此，我需要从我的查询(可能是视图)创建一个对象，在查询中搜索视图而不是表。 视图不接受绑定值，如果没有绑定，我无法插入开始日期和结束日期。有人对我如何完成这项工作有任何提示吗？提前致谢

我尝试创建仅在查询中包含日期列的视图，但是在(针对视图进行查询)之后无法使其工作

SELECT
t.medico,
t.periodo, 
t.capacidademaxima, 
t.disponiveis, 
t.usados, 
t.faltas, 
t.cancelamentos, 
t.pa, 
t.reforcopa, 
t.compromissoparicular, 
t.outrosempregos, 
t.reunioeshdp, 
t.reunioeslaboratorios, 
t.almoco,
t.exameseprocedimentos, 
t.hospitaisecirurgias, 
t.aulas, 
t.congresso, 
t.ferias, 
t.maternidade, 
t.tratamentomedico
FROM (
SELECT 
obter_desc_agenda(cd_agenda) medico,
'P1' periodo,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) capacidademaxima,
count(case when nvl(a.nr_seq_motivo_transf,0) not in (30) and a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end) pa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end) reforcopa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end)compromissoparicular,
count(case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end) outrosempregos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end) reunioeshdp,
count(case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end) reunioeslaboratorios,
count(case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end) ferias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end) almoco,
count(case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end) exameseprocedimentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end) hospitaisecirurgias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end) aulas,
count(case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end) congresso,
count(case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end) tratamentomedico,
count(case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end) maternidade
FROM
agenda_consulta a 
WHERE
trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final 
AND to_char(a.dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59')
GROUP BY
obter_desc_agenda(cd_agenda), 'P1'
UNION ALL
SELECT 
obter_desc_agenda(cd_agenda) medico,
'P2' periodo,
count(case when a.ie_status_agenda <> 'C' then 1 else null end) capacidademaxima,
count(case when nvl(a.nr_seq_motivo_transf,0) not in (30) and a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end) pa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end) reforcopa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end)compromissoparicular,
count(case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end) outrosempregos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end) reunioeshdp,
count(case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end) reunioeslaboratorios,
count(case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end) ferias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end) almoco,
count(case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end) exameseprocedimentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end) hospitaisecirurgias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end) aulas,
count(case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end) congresso,
count(case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end) tratamentomedico,
count(case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end) maternidade
FROM
agenda_consulta a 
WHERE trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final  
AND to_char (a.dt_agenda, 'HH24:MI:SS') between ('09:00:00') and ('11:59:59')
GROUP BY obter_desc_agenda(cd_agenda), 'P2'
UNION ALL

Then it repeats to P3, P4, P5 and the last part is "Total"

SELECT 
obter_desc_agenda(cd_agenda) medico,
'Total' periodo, 
count(case when a.ie_status_agenda <> 'C' then 1 else null end) capacidademaxima,
count(case when nvl(a.nr_seq_motivo_transf,0) not in (30) and a.ie_status_agenda <> 'C' then 1 else null end) disponiveis,
count(case when a.ie_status_agenda in ('E','L','N') then 1 else null end) usados,
count(case when a.ie_status_agenda in ('I') then 1 else null end) faltas,
count(case when a.ie_status_agenda in ('C') then 1 else null end) cancelamentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end) pa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end) reforcopa,
count(case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end)compromissoparicular,
count(case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end) outrosempregos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end) reunioeshdp,
count(case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end) reunioeslaboratorios,
count(case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end) ferias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end) almoco,
count(case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end) exameseprocedimentos,
count(case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end) hospitaisecirurgias,
count(case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end) aulas,
count(case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end) congresso,
count(case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end) tratamentomedico,
count(case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end) maternidade
FROM
agenda_consulta a 
WHERE trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final 
GROUP BY
obter_desc_agenda(cd_agenda), 'Total'
) t
order by medico, periodo

Answer 1

从此选择创建视图：

SELECT 
obter_desc_agenda(cd_agenda) medico,
case 
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59') then 'P1'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('09:00:00') and ('11:59:59') then 'P2'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('12:00:00') and ('14:59:59') then 'P3'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('15:00:00') and ('16:59:59') then 'P4'
when to_char(a.dt_agenda, 'HH24:MI:SS') between ('17:00:00') and ('19:59:59') then 'P5'
end periodo,
trunc(a.dt_agenda, 'dd') date_agenda,
case when a.ie_status_agenda <> 'C' then 1 else null end capacidademaxima, -- don't count yet - only set flags (1 or null)
case when nvl(a.nr_seq_motivo_transf,0) not in (30) and a.ie_status_agenda <> 'C' then 1 else null end disponiveis,
case when a.ie_status_agenda in ('E','L','N') then 1 else null end usados,
case when a.ie_status_agenda in ('I') then 1 else null end faltas,
case when a.ie_status_agenda in ('C') then 1 else null end cancelamentos,
case when nvl(a.nr_seq_motivo_transf,0) in (10) then 1 else null end pa,
case when nvl(a.nr_seq_motivo_transf,0) in (15) then 1 else null end reforcopa,
case when nvl(a.nr_seq_motivo_transf,0) in (28) then 1 else null end compromissoparicular,
case when nvl(a.nr_seq_motivo_transf,0) in (12) then 1 else null end outrosempregos,
case when nvl(a.nr_seq_motivo_transf,0) in (16) then 1 else null end reunioeshdp,
case when nvl(a.nr_seq_motivo_transf,0) in (26) then 1 else null end reunioeslaboratorios,
case when nvl(a.nr_seq_motivo_transf,0) in (20) then 1 else null end ferias,
case when nvl(a.nr_seq_motivo_transf,0) in (33) then 1 else null end almoco,
case when nvl(a.nr_seq_motivo_transf,0) in (27) then 1 else null end exameseprocedimentos,
case when nvl(a.nr_seq_motivo_transf,0) in (11) then 1 else null end hospitaisecirurgias,
case when nvl(a.nr_seq_motivo_transf,0) in (13) then 1 else null end aulas,
case when nvl(a.nr_seq_motivo_transf,0) in (14) then 1 else null end congresso,
case when nvl(a.nr_seq_motivo_transf,0) in (32) then 1 else null end tratamentomedico,
case when nvl(a.nr_seq_motivo_transf,0) in (31) then 1 else null end maternidade
from agenda_consulta a 

并像这样使用它：

select medico, nvl(periodo, 'Total') -- nvl is there because grouping only by medico(counting Total) gives null in periodo
, count(capacidademaxima)
, count(disponiveis)
, count(usados)
...
, count(tratamentomedico)
, count(maternidade)
from viewName
where date_agenda between :dt_inicial and :dt_final 
group by grouping sets((periodo, medico), (medico)) -- it's the same as doing group by periodo, medico (counts groups P1 to P5) union all group by medico (counts Total)

Answer 2

将trunc(a.dt_agenda, 'dd')带到SELECT-list并GROUP BY-list并按如下方式CREATE VIEW：

CREATE [OR REPLACE] VIEW v_agenda_consulta AS
SELECT trunc(a.dt_agenda, 'dd') as dt_agenda, 'P1' as periodo, count( case when ....
FROM agenda_consulta a 
WHERE to_char(a.dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59')
GROUP BY trunc(a.dt_agenda, 'dd'), obter_desc_agenda(cd_agenda), 'P1'
UNION ALL
SELECT trunc(a.dt_agenda, 'dd'), 'P2' , count( case when ....
FROM agenda_consulta a 
WHERE to_char(a.dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59')
GROUP BY trunc(a.dt_agenda, 'dd'), obter_desc_agenda(cd_agenda), 'P2'
UNION ALL
SELECT trunc(a.dt_agenda, 'dd'), 'Total' , count( case when ....
FROM agenda_consulta a 
WHERE to_char(a.dt_agenda, 'HH24:MI:SS') between ('07:00:00') and ('08:59:59')
GROUP BY trunc(a.dt_agenda, 'dd'), obter_desc_agenda(cd_agenda), 'Total';

您可以调用

SELECT *
FROM v_agenda_consulta 
WHERE dt_agenda between :dt_inicial and :dt_final

第一次尝试创建没有REPLACE选项的使用CREATE VIEW v_agenda_consulta AS ...时要注意。您可能有一个要覆盖的现有VIEW。