Message-ID: <3D529269.71D1F90D@exesolutions.com>
From: Daniel Morgan <dmorgan@exesolutions.com>
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: comp.database.oracle,comp.databases.oracle
Subject: Re: "ORA-00935: group function is nested too deeply" in oracle8.1.7
References: <59b71f2d.0208080156.1c7ef5b@posting.google.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 47
Date: Thu, 08 Aug 2002 15:46:54 GMT
NNTP-Posting-Host: 156.74.250.7
X-Complaints-To: yvonne.tracy@ci.seattle.wa.us
X-Trace: news-west.eli.net 1028821614 156.74.250.7 (Thu, 08 Aug 2002 09:46:54 MDT)
NNTP-Posting-Date: Thu, 08 Aug 2002 09:46:54 MDT
Organization: City of Seattle NewsReader Service


"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