使用 LISTAGG(DISTINCT ..) 时出现 Db2 错误"An expression in the ORDER BY clause .. is not valid."

Question

这个查询在Db2 v11.5.7.0中似乎是有效的:

SELECT listagg(DISTINCT x, ',') WITHIN GROUP (ORDER BY x)
FROM (VALUES (1), (1), (2)) t (x)

但是用

SQL Error [42822]: ORDER BY子句中以下位置或以"&quot开头的表达式在"顺序"中条款无效。原因码= "2"…SQLCODE=-214, SQLSTATE=42822, DRIVER=4.29.24

除了x本身，我还可以在ORDER BY子句中使用什么其他可能的东西?注意，问题与DISTINCT有关。如此:

SELECT listagg(x, ',') WITHIN GROUP (ORDER BY x)
FROM (VALUES (1), (1), (2)) t (x)

并产生预期输出:

1, 1、2

Answer 1

这是记录在案的行为。根据文档:

如果为LISTAGG指定了DISTINCT, ORDER BY规范的sort-key必须匹配字符串表达式(SQLSTATE 42822)。如果字符串表达式是隐式强制转换，则排序键必须显式地包含相应的匹配强制转换规范。

所以一个可能的解决方法是:

SELECT listagg(DISTINCT x, ',') WITHIN GROUP (ORDER BY CAST(x as VARCHAR))
FROM (VALUES (1), (1), (2)) t (x)

当然，该排序可能与预期的排序不同，因此此解决方法可能不适用于所有输入数据!