Sum resultado de UNION no Oracle

SQL

Oracle PL SQL

23/04/2019

Bom dia.
Estou tentando somar o resultado de dois aliases de queries que possuem UNION entre si, porém qualquer variável que não esteja no 1o bloco (ou seja, esteja depois do union) não consigo retornar, recebendo o erro 00904. 00000 - "%s: invalid identifier" no Sql Developer. Segue o SQL

select (t.tempo+t.tempospa) from (
SELECT to_char(trunc(sum(nr_minuto_duracao)/60),'FM00') || ':' || to_char(mod(sum(nr_minuto_duracao),60),'FM00') tempo, obter_desc_agenda(cd_agenda) medico,
decode(ie_status_agenda,'E','Presente','Presente') status
from agenda_consulta where trunc(dt_agenda, 'dd') between :dtinicio and :dtfim and ie_status_agenda in ('E','O')  group by obter_desc_agenda(cd_agenda),decode(ie_status_agenda,'E','Presente','Presente')
union all
SELECT to_char(trunc(sum(nr_minuto_duracao)/60),'FM00') || ':' || to_char(mod(sum(nr_minuto_duracao),60),'FM00') tempofaltaecancelados, obter_desc_agenda(cd_agenda) medico, 
decode(ie_status_agenda,'I','Falta','C','Cancelada') status
from agenda_consulta where trunc(dt_agenda, 'dd') between :dtinicio and :dtfim and ie_status_agenda in ('I','C') 
group by obter_desc_agenda(cd_agenda),decode(ie_status_agenda,'I','Falta','C','Cancelada') 
union all
SELECT to_char(trunc(sum(nr_minuto_duracao)/60),'FM00') || ':' || to_char(mod(sum(nr_minuto_duracao),60),'FM00') tempospa, obter_desc_agenda(cd_agenda) medico, NM_PACIENTE status
from agenda_consulta where trunc(dt_agenda, 'dd') between :dtinicio and :dtfim and UPPER(SUBSTR(NM_PACIENTE,1,15)) IN ('BLOQUEADO (SPA)','BLOQUEADO (POLI','BLOQUEADO (REUN' )  
group by obter_desc_agenda(cd_agenda),NM_PACIENTE order by medico asc) t


O tempospa não é reconhecido, assim como se eu colocar qualquer outra aliase para somar que não esteja no 1o bloco.
Alguém tem alguma luz sobre o motivo do não reconhecimento do aliase no union???

Obrigado
Diego

Diego

Curtidas 0

Melhor post

Emerson Nascimento

Emerson Nascimento

24/04/2019

O segundo exemplo ficou errado porque não precisava da SUM() ao transformar em texto:
SELECT
    t.medico,
    to_char(trunc(t.ntempo/60),'FM00') || ':' || to_char(mod(t.ntempo,60),'FM00') tempo,
    to_char(trunc(t.ntempofaltaecancelados/60),'FM00') || ':' || to_char(mod(t.ntempofaltaecancelados,60),'FM00') tempofaltaecancelados,
    to_char(trunc(t.ntempospa/60),'FM00') || ':' || to_char(mod(t.ntempospa,60),'FM00') tempospa
FROM (
    SELECT
        obter_desc_agenda(cd_agenda) medico,
        sum(case when ie_status_agenda in ('E','O') then nr_minuto_duracao else 0 end) ntempo,
        sum(case when ie_status_agenda in ('I','C') then nr_minuto_duracao else 0 end) ntempofaltaecancelados,
        sum(case when not (ie_status_agenda in ('E','O','I','C')) and upper(substr(NM_PACIENTE,1,15)) IN ('BLOQUEADO (SPA)','BLOQUEADO (POLI','BLOQUEADO (REUN') then nr_minuto_duracao else 0 end) ntempospa
    FROM
        agenda_consulta
    WHERE
        trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
    GROUP BY
        obter_desc_agenda(cd_agenda)
) t


pelo que eu entendi o que você precisa, no caso de apresentar o médico, é:
SELECT
    t.medico,
    to_char(trunc((t.ntempo+t.ntempospa)/60),'FM00') || ':' || to_char(mod((t.ntempo+t.ntempospa),60),'FM00') tempo
FROM (
    SELECT
        obter_desc_agenda(cd_agenda) medico,
        sum(case when ie_status_agenda in ('E','O') then nr_minuto_duracao else 0 end) ntempo,
        sum(case when ie_status_agenda in ('I','C') then nr_minuto_duracao else 0 end) ntempofaltaecancelados,
        sum(case when not (ie_status_agenda in ('E','O','I','C')) and upper(substr(NM_PACIENTE,1,15)) IN ('BLOQUEADO (SPA)','BLOQUEADO (POLI','BLOQUEADO (REUN') then nr_minuto_duracao else 0 end) ntempospa
    FROM
        agenda_consulta
    WHERE
        trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
    GROUP BY
        obter_desc_agenda(cd_agenda)
) t


ou para obter o total geral:
SELECT
    to_char(trunc(sum(t.ntempo+t.ntempospa)/60),'FM00') || ':' || to_char(mod(sum(t.ntempo+t.ntempospa),60),'FM00') tempo
FROM (
    SELECT
        obter_desc_agenda(cd_agenda) medico,
        sum(case when ie_status_agenda in ('E','O') then nr_minuto_duracao else 0 end) ntempo,
        sum(case when ie_status_agenda in ('I','C') then nr_minuto_duracao else 0 end) ntempofaltaecancelados,
        sum(case when not (ie_status_agenda in ('E','O','I','C')) and upper(substr(NM_PACIENTE,1,15)) IN ('BLOQUEADO (SPA)','BLOQUEADO (POLI','BLOQUEADO (REUN') then nr_minuto_duracao else 0 end) ntempospa
    FROM
        agenda_consulta
    WHERE
        trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
    GROUP BY
        obter_desc_agenda(cd_agenda)
) t

GOSTEI 1

Mais Respostas

Emerson Nascimento

Emerson Nascimento

23/04/2019

tente algo assim:
SELECT
	obter_desc_agenda(cd_agenda) medico,
	sum(case when ie_status_agenda in ('E','O') then nr_minuto_duracao else 0 end) ntempo,
	sum(case when ie_status_agenda in ('I','C') then nr_minuto_duracao else 0 end) ntempofaltaecancelados,
	sum(case when not (ie_status_agenda in ('E','O','I','C')) and upper(substr(NM_PACIENTE,1,15)) IN ('BLOQUEADO (SPA)','BLOQUEADO (POLI','BLOQUEADO (REUN') then nr_minuto_duracao else 0 end) ntempospa
FROM
	agenda_consulta
WHERE
	trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
GROUP BY
	obter_desc_agenda(cd_agenda)


para apresentar da forma que você exemplificou:
SELECT
	t.medico,
	to_char(trunc(sum(t.ntempo)/60),'FM00') || ':' || to_char(mod(sum(t.ntempo),60),'FM00') tempo,
	to_char(trunc(sum(t.ntempofaltaecancelados)/60),'FM00') || ':' || to_char(mod(sum(t.ntempofaltaecancelados),60),'FM00') tempofaltaecancelados,
	to_char(trunc(sum(t.ntempospa)/60),'FM00') || ':' || to_char(mod(sum(t.ntempospa),60),'FM00') tempospa
FROM (
	SELECT
		obter_desc_agenda(cd_agenda) medico,
		sum(case when ie_status_agenda in ('E','O') then nr_minuto_duracao else 0 end) ntempo,
		sum(case when ie_status_agenda in ('I','C') then nr_minuto_duracao else 0 end) ntempofaltaecancelados,
		sum(case when not (ie_status_agenda in ('E','O','I','C')) and upper(substr(NM_PACIENTE,1,15)) IN ('BLOQUEADO (SPA)','BLOQUEADO (POLI','BLOQUEADO (REUN') then nr_minuto_duracao else 0 end) ntempospa
	FROM
		agenda_consulta
	WHERE
		trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
	GROUP BY
		obter_desc_agenda(cd_agenda)
) t

Fiz baseado na tua instrução (não tenho Oracle; uso somente SQLServer)
GOSTEI 0
Diego

Diego

23/04/2019

Olá Emerson, o 1o exemplo funcionou, mas ele não tem a tratativa para converter o campo nr_minuto_duracao(number) para o fomato de horas e minutos. Dessa forma, todos os resultados são apresentados em minutos. Não sei se consigo tratar a "conversão" dentro do Case.
No segundo exemplo, ao colocar o código exatamente como veio recebi a mensagem: ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function", ou seja que precisava de agrupamento(devido as funções sum no select de cima), tentei agrupar mas não funcionou.
Alterando o código para o código abaixo, mas novamente caio no problema de depender da conversão dos minutos em number dentro do case para um char mostrando horas e minutos. Até onde sei não consigo formatar o nr_minuto_duracao dentro do case após o then, a exemplo do que estava fazendo antes (to_char(trunc(sum(nr_minuto_duracao)/60),'FM00')

SELECT
    t.medico,(t.ntempo+t.ntempospa) totaltempopresente,
    t.ntempo,
    t.ntempofaltaecancelados,
    t.ntempospa
FROM (
    SELECT
        obter_desc_agenda(cd_agenda) medico,
        sum(case when ie_status_agenda in ('E','O') then nr_minuto_duracao else 0 end) ntempo,
        sum(case when ie_status_agenda in ('I','C') then nr_minuto_duracao else 0 end) ntempofaltaecancelados,
        sum(case when not (ie_status_agenda in ('E','O','I','C')) and upper(substr(NM_PACIENTE,1,15)) IN ('BLOQUEADO (SPA)','BLOQUEADO (POLI','BLOQUEADO (REUN') then nr_minuto_duracao else 0 end) ntempospa
    FROM
        agenda_consulta
    WHERE
        trunc(dt_agenda, 'dd') between :dtinicio and :dtfim
    GROUP BY
        obter_desc_agenda(cd_agenda)
) t


GOSTEI 0
Diego

Diego

23/04/2019

Perfeito Emerson, era isso mesmo. Não sei porque os aliases não estavam sendo lidos dentro do UNION, mas dessa forma que você colocou, apenas com subqueries funcionou perfeitamente. MUITO obrigado
GOSTEI 0
POSTAR