OraFAQ Forum: Reports & Discoverer » Nested Aggregate Function not allowed

Home » Developer & Programmer » Reports & Discoverer » Nested Aggregate Function not allowed

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Nested Aggregate Function not allowed [message #169423]

Wed, 26 April 2006 14:53

jillian724
Messages: 1
Registered: April 2006
Location: CA

Junior Member

Disclaimer: I am not a programmer/DBA, so, please be easy on me.

I am trying to query a db to get the highest recruiting status for a candidate but there is not an underlying table that assigns a number to each status . To work around this, I want to create a decode to assign a number for each status in order of our process. Example:

(statusorder)
DECODE(Status,"Resume","1","Interview","2","Offer","3","Hired","4")

Then I want to do a MAX on this decode. MAX(statusorder)

I am getting the nested aggregate function not allowed message.

Could I workaround this by requesting a separate view be set up in the same business area to contain statusorder and statusorderMAX? Or would the dba run into the same nested aggregate not allowed error? I like to try to know as much information up front before bugging our dba. Thanks!

Report message to a moderator

Re: Nested Aggregate Function not allowed [message #169544 is a reply to message #169423]

Thu, 27 April 2006 09:36

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Hi Jillian,

Could you explain in more detail what your selection is? Because while trying to reproduce your problem, I found out that the following calculation on employees (standard demo table in Oracle) works fine:

MAX(DECODE(Job Id,'ST_MAN','1','ST_CLERK','2','PU_CLERK','3','4'))

So, obviously, I'm missing something in your story. Oh and what version of Discoverer are you working with?

Regards,
Sabine

Report message to a moderator

Re: Nested Aggregate Function not allowed [message #169595 is a reply to message #169544]

Thu, 27 April 2006 12:31

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Discoverer? I thought it was about Report Builder Embarassed

Never mind ... although you haven't provided actual query, I guess this is a query that won't work:

SELECT c.name, 
       MAX(DECODE(c.status, 'resume', 1, 'interview', 2, 'offer', 3, 'hired', 4)) status
FROM CANDIDATES c
GROUP BY c.name
ORDER BY 1, 2;

If MAX and DECODE won't work together, perhaps something like this will:

SELECT b.name, MAX(b.status) status
FROM (
      SELECT c.name, 
	       DECODE(c.status, 'resume', 1, 'interview', 2, 'offer', 3, 'hired', 4) status
      FROM CANDIDATES c
	 ) b
GROUP BY b.name
ORDER BY 1, 2;

Report message to a moderator

Previous Topic:	Bursting
Next Topic:	Rep-0999 Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 02:37:58 CDT 2024