Oracle SQL中LISTAGG聚合存在ECI时排除CI的实现问题

问题原因说明 #

之前触发ORA-00905 缺失关键字错误的核心原因：WHERE是行级过滤子句，你需要判断的**全表是否存在ECI**属于聚合级的全局判断，不能直接在行级WHERE中通过CASE引用全局状态，同时语法书写不符合Oracle规范也会触发该报错。

可行解决方案 #

方案1：全版本兼容方案

先通过临时表构造全局ECI存在性标识，再根据标识过滤行后聚合，兼容所有Oracle版本：

WITH check_eci_flag AS (
    -- 判断全表是否存在ECI值
    SELECT CASE 
             WHEN EXISTS(SELECT 1 FROM test_table WHERE DOCUMENT_TYPE_CD = 'ECI') 
             THEN 1 
             ELSE 0 
           END AS has_eci
    FROM DUAL
)
SELECT LISTAGG(d.DOCUMENT_TYPE_CD, ',') WITHIN GROUP (ORDER BY d.DOCUMENT_TYPE_CD) AS value
FROM test_table d, check_eci_flag f
-- 存在ECI时排除CI，否则不过滤
WHERE NOT (f.has_eci = 1 AND d.DOCUMENT_TYPE_CD = 'CI');

方案2：Oracle 12cR2及以上版本简化方案

12cR2开始支持聚合函数的FILTER修饰符，语法更简洁无需额外关联临时表：

SELECT LISTAGG(d.DOCUMENT_TYPE_CD, ',')
       FILTER (WHERE NOT (
           EXISTS(SELECT 1 FROM test_table WHERE DOCUMENT_TYPE_CD = 'ECI') 
           AND d.DOCUMENT_TYPE_CD = 'CI'
       ))
       WITHIN GROUP (ORDER BY d.DOCUMENT_TYPE_CD) AS value
FROM test_table d;

两种方案执行效果完全一致：当表中存在ECI值时，聚合结果自动排除CI，返回ECI,POA；如果表中不存在ECI值，则保留所有文档类型编码正常聚合。

内容的提问来源于stack exchange，提问作者Bjawww