LISTAGG Request "ORA-00937: not a group group function"

Question

I am trying to create a connection with a comma separated list of values. For instance:

rule_id | attribute_id
----------------------
1       | a
1       | b
2       | c
2       | d

it should be:

rule_id | attribute_id
----------------------
1       | a,b
2       | c,d

I am trying to do this using LISTAGG. However, with the code below, I get ORA-00937: not a single-group group function. I noticed the syntax FOR PATHfor the sql server, but it doesn't seem to work for our configuration. Here is my request:

SELECT r.rule_id as RULE_ID, 
LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) "ATTR_IDS"
FROM N_RULE r, N_ATTRIBUTE a 
WHERE r.RULE_ID = a.RULE_ID 
ORDER BY r.AUDIENCE, UPPER(r.NAME);

Answer 1

I think that for your job request you need to add group by, change order by. You should also use the correct explicit join syntax:

SELECT r.rule_id as RULE_ID, 
       LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) as "ATTR_IDS"
FROM N_RULE r JOIN
     N_ATTRIBUTE a 
     ON r.RULE_ID = a.RULE_ID 
GROUP BY r.rule_id
ORDER BY r.rule_id;

Or maybe you want to include another attribute in the results:

SELECT r.rule_id, r.AUDIENCE, UPPER(r.NAME) 
       LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) as "ATTR_IDS"
FROM N_RULE r JOIN
     N_ATTRIBUTE a 
     ON r.RULE_ID = a.RULE_ID 
GROUP BY r.rule_id, r.AUDIENCE, UPPER(r.NAME)
ORDER BY r.AUDIENCE, UPPER(r.NAME);