Home » SQL & PL/SQL » SQL & PL/SQL » Error ORA-00978
icon9.gif  Error ORA-00978 [message #234246] Mon, 30 April 2007 10:22 Go to next message
vqueiroz
Messages: 5
Registered: April 2007
Location: Brasil
Junior Member
What's wrong in this query? Because without T_AUX_FORNECIMENTO.ID_DESTINO was worked.

SELECT DISTINCT
T_AUX_FORNECIMENTO.ID_DESTINO,
NVL(SUM(T_AUX_FORNECIMENTO.NU_QTD * COUNT(DISTINCT T_AUX_FORNECIMENTO.ID_AUX_FORNECIMENTO)), 0) AS QTDEMP,
NVL(SUM(NVL(T_AUX_FORNECIMENTO.NU_QTD_DE_VOLUMES, 0) * COUNT(DISTINCT T_AUX_FORNECIMENTO.ID_AUX_FORNECIMENTO)), 0) AS NU_QTD_DE_VOLUMES
FROM
T_AUX_FORNECIMENTO,
T_FORNECIMENTO
WHERE
T_AUX_FORNECIMENTO.ID_FORNECIMENTO = T_FORNECIMENTO.ID_FORNECIMENTO AND
(T_FORNECIMENTO.BT_COMPLETO = 2 OR T_FORNECIMENTO.BT_COMPLETO = 5 OR T_FORNECIMENTO.BT_COMPLETO = 15 OR T_FORNECIMENTO.BT_COMPLETO = 12) AND
(T_AUX_FORNECIMENTO.BT_DELETADO = 0) AND
(T_FORNECIMENTO.BT_DELETADO = 0) AND
(T_AUX_FORNECIMENTO.ID_MUNICAO = 612) AND
(T_AUX_FORNECIMENTO.NU_LOTE = 'J 030-001') AND
(T_AUX_FORNECIMENTO.NU_ANO = 1999) AND
(T_AUX_FORNECIMENTO.TX_SIGLA = 'CBC') AND
(T_AUX_FORNECIMENTO.ID_CATEGORIA = 3) AND
(T_AUX_FORNECIMENTO.ID_SITUACAO_MUNICAO = Cool AND
(T_AUX_FORNECIMENTO.ID_LOCALIZACAO = 9)
GROUP BY
T_AUX_FORNECIMENTO.ID_DESTINO,
T_AUX_FORNECIMENTO.NU_QTD,
T_AUX_FORNECIMENTO.NU_QTD_DE_VOLUMES



Re: Error ORA-00978 [message #234262 is a reply to message #234246] Mon, 30 April 2007 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Format your post, read How to format your posts
2/ ALWAYS post your Oracle version
3/ You have to use a subquery to count and then sum in the outer query:
select destino, sum(qtd*cnt), sum(qtd_de_vol*cnt)
from (
select destino, qtd, qtd_de_vol, count(...) cnt
from ...
where ...
group by destino, qtdn qtd_de_vol
)
group by destino
/

Regards
Michel
Re: Error ORA-00978 [message #234267 is a reply to message #234246] Mon, 30 April 2007 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
00978, 00000, "nested group function without GROUP BY"
// *Cause:
// *Action:
Re: Error ORA-00978 [message #234274 is a reply to message #234267] Mon, 30 April 2007 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ana, this is what I first thought to post but this is not very helpful.
Nested group function is a difficult one in SQL. This is why I posted a solution but I don't know if it actually helps Vivian (vqueiroz) as she does not reply.

Regards
Michel
Re: Error ORA-00978 [message #234285 is a reply to message #234262] Mon, 30 April 2007 13:25 Go to previous messageGo to next message
vqueiroz
Messages: 5
Registered: April 2007
Location: Brasil
Junior Member
Thanks Michel
icon10.gif  Re: Error ORA-00978 [message #234288 is a reply to message #234285] Mon, 30 April 2007 13:30 Go to previous message
vqueiroz
Messages: 5
Registered: April 2007
Location: Brasil
Junior Member
You help me a lot.
Previous Topic: Creating a 'grouping' view
Next Topic: Can trigger autostart on Specific Dates
Goto Forum:
  


Current Time: Sat Dec 10 02:55:23 CST 2016

Total time taken to generate the page: 0.08096 seconds