OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » LISTAGG (11g)

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

LISTAGG [message #573881]

Thu, 03 January 2013 16:44

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.

Report message to a moderator

Re: LISTAGG [message #573884 is a reply to message #573881]

Thu, 03 January 2013 17:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573889 is a reply to message #573881]

Thu, 03 January 2013 19:33

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

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573890 is a reply to message #573889]

Thu, 03 January 2013 19:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573892 is a reply to message #573890]

Thu, 03 January 2013 20:06

tgreen1
Messages: 13
Registered: December 2012

Junior Member

I'm using SQL Navigator. It's still giving me the ORA-00937 error.

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573894 is a reply to message #573892]

Thu, 03 January 2013 21:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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;

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573936 is a reply to message #573894]

Fri, 04 January 2013 07:04

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.

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573937 is a reply to message #573936]

Fri, 04 January 2013 07:12

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which problems still have you?

Regards
Michel

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573944 is a reply to message #573937]

Fri, 04 January 2013 08:54

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

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573945 is a reply to message #573944]

Fri, 04 January 2013 08:56

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: LISTAGG - ORA-00937 [message #573955 is a reply to message #573945]

Fri, 04 January 2013 09:34

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.

Report message to a moderator

Previous Topic:	Update query using case when exists
Next Topic:	How to add Oracle constraint to below tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Oct 31 11:47:49 CDT 2025