sqloracle-databaseora-00923

ORACLE SQL listagg function

I'm not sure what's going on here, mostly because I've never used this function, but when I use the listagg function on our Oracle 11g database it gives me an ORA-00923 FROM keyword not found where expected.

Here's my SQL

SELECT  cdm.courses_id,cde.additional_resources, listagg (dm.delivery_method_desc, ',')
WITHIN GROUP (ORDER BY dm.delivery_method_desc) delivery_methods
FROM    tablespace.course_de_delivery_methods cdm,
      tablespace.course_distance_ed cde,
      tablespace.delivery_methods dm
WHERE   cdm.courses_id = cde.courses_id
AND   cdm.delivery_methods_id = dm.delivery_methods_id
GROUP BY cdm.courses_id

I haven't a clue why this is breaking. I was following the example found here.

Solution

  • Are you using 11.1 or 11.2? LISTAGG was introduced in 11.2, it was not available in 11.1.

    Your SQL statement looks valid to me in 11.2. But you'd get an error in 11.1 and ORA-00923 would seem like a reasonable error in 11.1.