Home » Developer & Programmer » Reports & Discoverer » Nested Aggregate Function not allowed
Nested Aggregate Function not allowed [message #169423] |
Wed, 26 April 2006 14:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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!
|
|
|
Re: Nested Aggregate Function not allowed [message #169544 is a reply to message #169423] |
Thu, 27 April 2006 09:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Nested Aggregate Function not allowed [message #169595 is a reply to message #169544] |
Thu, 27 April 2006 12:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Discoverer? I thought it was about Report Builder ![Embarassed](images/smiley_icons/icon_redface.gif)
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;
|
|
|
Goto Forum:
Current Time: Wed Sep 11 20:30:18 CDT 2024
|