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

Are :dt_inicial and :dt_final somehow relative to sysdate ? Could you just write trunc(a.dt_agenda, 'dd') between sysdate-2 and sysdate ? — OjtamOjtam, Oct 22 '19 at 20:06
No, unfortunately they are not sysdate related. The values may change anytime like :startdate and :finaldate — Dosc84, Oct 23 '19 at 11:04

score 0 · Answer 1 · answered Oct 23 '19 at 05:23

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.

Thanks for the reply, but I can't group by date, since it'll make the "Periods" repeat for each date, that's why I tried to place the date in a subquery. As you can see in the two images, the one above is date grouped, and the second one is not(which is my desired result) . https://photos.app.goo.gl/vcY3q34qiVkY4nmp8 — Dosc84, Oct 23 '19 at 10:24
And if I try to put the date in a subquery, like this: SELECT obter_desc_agenda(cd_agenda) medico, (select trunc(dt_agenda, 'dd') from agenda_consulta) as dt_agenda, 'P1' as periodo, and then removing from the GROUP BY, I get an error while trying to query the view. For example: SELECT * FROM HORARIOSDISPONIVEIS WHERE dt_agenda between :dt_inicial and :dt_final .... That returns ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" — Dosc84, Oct 23 '19 at 10:32
It returns `ORA-01427` because you have no `where` clause in `(select trunc(dt_agenda, 'dd') from agenda_consulta)` so it tries to select every dt_agenda in the table. — OjtamOjtam, Oct 23 '19 at 18:40

score 0 · Accepted Answer · answered Oct 23 '19 at 19:35

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)

God you are a genious. I had the job done, but with a way more complex solution. I've made a function to return a table... `CREATE OR REPLACE TYPE REL_HORARIOS_type AS OBJECT ( medico VARCHAR2(1000), periodo VARCHAR2(100),...` Then `CREATE OR REPLACE TYPE REL_HORARIOS_table_type AS TABLE OF REL_HORARIOS_type;....` Then... `CREATE OR REPLACE FUNCTION HORARIOSDISPONIVEIS(dt_inicial DATE, dt_final DATE) RETURN REL_HORARIOS_table_type PIPELINED AS BEGIN FOR v_Rec IN (SELECT ....` — Dosc84, Oct 24 '19 at 13:24

Transforming a Query into a View

2 Answers2