Transforming a Query into a View

Question

I have a long query that counts a few itens, based on periods. With that said, I have period P1 (which translates into 07:00:00 and 08:59:59, and so it goes. I have to calculate all the counts by medic and period . I use a binded value to search the table(trunc(a.dt_agenda, 'dd') between :dt_inicial and :dt_final ) Since I use UNION ALL to put all the periods together, the query is quite longs, and I need to store it in a varchar2 field (4000). So, I need to create an object from my query (probably a view), in a way that in the query searches the view, instead of the table. Views don't accept binded values and without the bind I can't insert the start date and end date. Anyone has any tip on how I could make this work? Thanks in advance

I've tried creating the view just including the date column in the query, but I wasn't able to make it work after (querying against the view)

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

Create view from this select :

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 

and use it like this:

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

Take trunc(a.dt_agenda, 'dd') to SELECT-list and GROUP BY-list and CREATE VIEW as below :

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';

And you can call as

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

Pay attention in the first try to create use CREATE VIEW v_agenda_consulta AS ... without REPLACE option. You might have an existing VIEW to be overridden.