Re: "ORA-00935: group function is nested too deeply" in oracle8.1.7

"Mary.M" wrote:

> Hi, all gurus,
>
> I encounter a very weird thing, who can help me?
>
> I have a SQL statement:
> "select IPAddr,sum(Flow)/1048576 Flow,avg(FlowRate)/1024 FlowRate from
> (select SrcAddr as IPAddr,sum(Octets) Flow,sum(Octets)/(count(*) *
> 86400) FlowRate from TrafficComingDailyData where DestAddr =
> '192.168.0.130' and StartTime >= to_date('2002-8-1','yyyy-mm-dd
> hh24:mi:ss') and StartTime < to_date('2002-8-8 17:8','yyyy-mm-dd
> hh24:mi:ss') group by SrcAddr
> union all
> select DestAddr as IPAddr,sum(Octets) Flow,sum(Octets)/(count(*) *
> 86400) FlowRate from TrafficGoingDailyData where SrcAddr =
> '192.168.0.130' and
> StartTime >= to_date('2002-8-1','yyyy-mm-dd hh24:mi:ss') and
> StartTime < to_date('2002-8-8 17:8','yyyy-mm-dd hh24:mi:ss') group by
> DestAddr)
> group by IPAddr order by sum(Flow) desc"
>
> When I run it under oracle8.1.5, there is no prblem.
> But when I run it under oracle8.1.7, error happends:
> " group by IPAddr order by sum(Flow) desc
> *
> ERROR at line 10:
> ORA-00935: group function is nested too deeply".
>
> What is the reason?
> Thanks in advance!
> By Mary.M.

Usually it is a case of missing parentheses around a group function and that may be the case here though it doesn't jump right out at me on a cursory look. Try it and see.

Otherwise ... the following was written by Oracle support:

"it appears that the issue in your case (another person's query) is that your query expression criteria combining the sum and count functions is too complex for the group by.

Go figure.

Daniel Morgan Received on Thu Aug 08 2002 - 17:46:54 CEST