ORACLE SQL listagg函数 [英] ORACLE SQL listagg function

问题描述

我不确定这是怎么回事，主要是因为我从未使用过此函数，但是当我在Oracle 11g数据库上使用listagg函数时，会给我一个ORA-00923 FROM关键字，找不到预期的位置. /p>

这是我的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

我不知道为什么会这样.我正在遵循在此处找到的示例.

解决方案

您使用的是11.1还是11.2? LISTAGG在11.2中引入，但在11.1中不可用.

您的SQL语句在11.2中对我来说是有效的.但是您会在11.1中遇到错误，而ORA-00923在11.1中似乎是一个合理的错误.

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.

解决方案

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.

这篇关于ORACLE SQL listagg函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！