Home » SQL & PL/SQL » SQL & PL/SQL » LISTAGG (11g)
LISTAGG [message #573881] Thu, 03 January 2013 16:44 Go to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
I'm trying to use the LISTAGG function, but I keep getting an ORA-00937 error (not a single-group group function). I'm concatenating several columns into one, and that is what I want to LISTAGG on. Can anyone tell me what I'm doing wrong? See below.

select distinct COLUMN1, LISTAGG(upper('LABEL: ' || COLUMN2 || '-' || COLUMN3 || '.' || COLUMN4),';')
WITHIN GROUP (ORDER BY COLUMN2) as COLUMN_NAME
from DUMMY
inner join DUMMY2 on COLUMN1=COLUMN2

Any suggestions would be appreciated.

Thanks.
Re: LISTAGG [message #573884 is a reply to message #573881] Thu, 03 January 2013 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: LISTAGG - ORA-00937 [message #573889 is a reply to message #573881] Thu, 03 January 2013 19:33 Go to previous messageGo to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
I keep getting an ORA-00937 when executing the following code. I'm not sure what I'm doing wrong. Please help.

19:29:17 **** SCRIPT STARTED: 03-Jan-2013 19:29:17 ****
19:29:17 select distinct r.id USER_ID, r.first_last USER_NAME, r.role ROLE, LISTAGG(upper('PDD: ' || p.role_id || '-' || p.parameter || '.' || p.value), ';')
19:29:17 WITHIN GROUP (ORDER BY r.id,r.first_last, r.role) as p_responsibility
19:29:17 from it_users r inner join app_user_security_map p on r.id=p.user_id
19:29:17 line 1: ORA-00937: not a single-group group function
19:29:17 **** SCRIPT ENDED 03-Jan-2013 19:29:17 ****
19:29:17 End Script Execution

Thanks
Re: LISTAGG - ORA-00937 [message #573890 is a reply to message #573889] Thu, 03 January 2013 19:39 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
amazing that the query morphed between the two postings!
SELECT DISTINCT r.id                                                 USER_ID, 
                r.first_last                                         USER_NAME, 
                r.ROLE                                               ROLE, 
                Listagg(Upper('PDD: ' 
                              || p.role_id 
                              || '-' 
                              || p.parameter 
                              || '.' 
                              || p.value), ';') 
                  within GROUP (ORDER BY r.id, r.first_last, r.ROLE) AS 
                p_responsibility 
FROM   it_users r 
       inner join app_user_security_map p 
               ON r.id = p.user_id 


I am curious as which tool was used to produce the most recent results?
Re: LISTAGG - ORA-00937 [message #573892 is a reply to message #573890] Thu, 03 January 2013 20:06 Go to previous messageGo to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
I'm using SQL Navigator. It's still giving me the ORA-00937 error.
Re: LISTAGG - ORA-00937 [message #573894 is a reply to message #573892] Thu, 03 January 2013 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
SELECT department_id "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

Re: LISTAGG - ORA-00937 [message #573936 is a reply to message #573894] Fri, 04 January 2013 07:04 Go to previous messageGo to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
Thanks for your suggestions. I'm still having some problems with it, but I'll keep at it.

Re: LISTAGG - ORA-00937 [message #573937 is a reply to message #573936] Fri, 04 January 2013 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which problems still have you?

Regards
Michel
Re: LISTAGG - ORA-00937 [message #573944 is a reply to message #573937] Fri, 04 January 2013 08:54 Go to previous messageGo to next message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
This is the revised code. Adding the "GROUP BY" as BlackSwan suggested did fix the ORA-00937 error. I'm now getting a ORA-01489 error. However, I think I will try to just LISTAGG on the value and see how that goes.

SELECT DISTINCT r.id USER_ID,
r.first_last USER_NAME,
r.ROLE ROLE,
Listagg(Upper('PDD: '
|| p.role_id
|| '-'
|| p.parameter
|| '.'
|| p.value), ';')
within GROUP (ORDER BY r.id, r.first_last, r.ROLE) AS
p_responsibility
FROM itr_users r
inner join app_user_security_map p
ON r.id = p.user_id GROUP BY r.id, r.first_last, r.ROLE

Thanks
Re: LISTAGG - ORA-00937 [message #573945 is a reply to message #573944] Fri, 04 January 2013 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.
We have not your table and so can't see what you see.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

ORA-01489: result of string concatenation is too long
 *Cause: String concatenation result is more than the maximum size.
 *Action: Make sure that the result is less than the maximum size.

ALWAYS post the complete error message we have NOT in mind all the 99999 error messages Oracle can send.

Regards
Michel
Re: LISTAGG - ORA-00937 [message #573955 is a reply to message #573945] Fri, 04 January 2013 09:34 Go to previous message
tgreen1
Messages: 13
Registered: December 2012
Junior Member
This gave me desired results.

SELECT DISTINCT r.id                                                 USER_ID, 
                r.first_last                                         USER_NAME, 
                r.ROLE                                               ROLE, 
                Upper('PDD: ' || p.role_id || '-' || p.parameter || '.' || LISTAGG(p.value,',') WITHIN GROUP (ORDER BY p.value)) 
                  AS p_responsibility 
FROM   itr_users r 
       inner join app_user_security_map p 
               ON r.id = p.user_id GROUP BY r.id, r.first_last, r.ROLE, p.role_id, p.parameter


Thank each of you for your help.
Previous Topic: Update query using case when exists
Next Topic: How to add Oracle constraint to below tables
Goto Forum:
  


Current Time: Tue Jul 29 21:13:17 CDT 2014

Total time taken to generate the page: 0.08711 seconds