Oracle中LISTAGG结合CASE WHEN长度超限 XMLAGG替代报错问题咨询

报错原因 #

你之前代码报missing keyword的核心问题是XMLELEMENT中的CASE WHEN语句缺少END结束符，同时分隔符的拼接位置错误。

xmlagg完全可以和CASE WHEN组合使用，正确写法如下：

SELECT 
    date AS "Some Date",
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS "active id numbers",
    RTRIM(
        XMLAGG(
            XMLELEMENT(E, CASE WHEN status = 'active' THEN id_number || ',' END)
            .EXTRACT('//text()') 
            ORDER BY id_number
        ).GetClobVal(),
        ','
    ) AS "List of active id numbers"
FROM mytable
GROUP BY date;

方案说明 #

• 该方案返回CLOB类型结果，最大支持4G内容存储，完全规避了原生listagg 4000字节的长度限制，不需要对结果做截断
• 非active状态的ID会被CASE WHEN返回为NULL，XMLAGG会自动忽略NULL值，和你原来使用listagg的逻辑完全一致
• 上述方案从Oracle 11g版本开始即可使用，兼容性良好。

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