如果Listagg用作条件表达式中的窗口函数，则会生成ORA-01489-数据库问题

listagg produces ORA-01489 if used as window function in conditional expression(如果Listagg用作条件表达式中的窗口函数，则会生成ORA-01489)

本文介绍了如果Listagg用作条件表达式中的窗口函数，则会生成ORA-01489的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的查询返回许多(数千)行。列l对于非常少的行数(最多10行)具有特定值。对于每个这样的行，我希望在所有这些行中输出非常短(最多5个字符)的varchar列v的逗号分隔值。对于没有特殊值l的行，我只想输出该行的v值。相同问题的综合示例：从前10000个整数开始，我想为每个一位数输出1,2,3,4,5,6,7,8,9；对于多位数输出该数字。(是的，这是一个愚蠢的例子，但是真实的案例是有意义的。)

with x (v,l) as (
  select to_char(level), length(to_char(level)) from dual connect by level <= 10000
)
select case l
         when 1 then listagg(v,',') within group (order by v) over (partition by l)
         else v
       end
from x
order by 1;

问题是，listagg函数在ORA-01489: result of string concatenation is too long错误时失败。

我知道listagg函数的4000个字符限制以及基于xmlagg的解决方法。我只是没有得到我想要连接的数据的限制是足够的，即使不是对所有数据都足够。在上面的示例中，9个一位数的分区可以容纳4000个字符，而9000个四位数的分区则不能。我原以为case表达式会阻止执行不相关行的窗口，但由于某种原因，数据库引擎似乎对所有行的窗口求值。(还要注意，order by子句导致查询快速失败-如果没有它，一些行将在失败前返回。)

你能解释一下这种行为的原因吗？我怀疑窗口计算逻辑上在select子句之前，但没有任何证据。在Oracle 11g、18c和19(Livesql)上复制。

推荐答案

好吧，您使用的SQL不是过程性的，因此不能期望代码路径的某些部分不会执行，仅仅是因为它们没有被使用。(因此，按照其他人的建议填充错误将不会成功)。

无论如何，您都可以根据listagg忽略null值这一事实来执行常用的技巧。

因此此公式运行良好：

with x (v,l) as ( select to_char(level), length(to_char(level)) from dual connect by level <= 10000 ) select nvl(listagg(case when l = 1 then v end,',') within group (order by v) over (partition by l),v) lst from x order by 1;

赠送
LST ------------------ 1,2,3,4,5,6,7,8,9 1,2,3,4,5,6,7,8,9 .. 10 100 1000 10000

问题说明见执行计划(仅显示相关部分)

---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 35 | 4 (50)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 35 | 4 (50)| 00:00:01 | | 2 | WINDOW SORT | | 1 | 35 | 4 (50)| 00:00:01 | | 3 | VIEW | | 1 | 35 | 2 (0)| 00:00:01 | |* 4 | CONNECT BY WITHOUT FILTERING| | | | | | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- ... Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) CASE "L" WHEN 1 THEN LISTAGG("V",',') WITHIN GROUP ( ORDER BY "V") OVER ( PARTITION BY "L") ELSE "V" END [4000] 2 - (#keys=2) "L"[NUMBER,22], "V"[VARCHAR2,40], LISTAGG("V",',') WITHIN GROUP ( ORDER BY "V") OVER ( PARTITION BY "L")[4000] 3 - "V"[VARCHAR2,40], "L"[NUMBER,22] 4 - LEVEL[4]

因此，计算第2行中的listagg仅在第1行中筛选listagg。

这篇关于如果Listagg用作条件表达式中的窗口函数，则会生成ORA-01489的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

问题描述

推荐答案

基础教程推荐