Этот запрос кажется действительным в Db2 v11.5.7.0:
SELECT listagg(DISTINCT x, ',') WITHIN GROUP (ORDER BY x)
FROM (VALUES (1), (1), (2)) t (x)
Но это не удается с:
Ошибка SQL [42822]: выражение в предложении ORDER BY в следующей позиции или начинающееся с «» в предложении «ORDER BY» недопустимо. Код причины = "2".. SQLCODE=-214, SQLSTATE=42822, ДРАЙВЕР=4.29.24
Что еще я мог бы использовать в предложении ORDER BY
, кроме самого x
? Обратите внимание, что проблема связана с DISTINCT
. Это работает:
SELECT listagg(x, ',') WITHIN GROUP (ORDER BY x)
FROM (VALUES (1), (1), (2)) t (x)
И выдает ожидаемый результат:
1,1,2
Это задокументированное поведение. Согласно документам:
Если для LISTAGG указано DISTINCT, ключ сортировки спецификации ORDER BY должен соответствовать строковому выражению (SQLSTATE 42822). Если строковое выражение приведено неявно, ключ сортировки должен явно включать соответствующую соответствующую спецификацию приведения.
Итак, возможный обходной путь:
SELECT listagg(DISTINCT x, ',') WITHIN GROUP (ORDER BY CAST(x as VARCHAR))
FROM (VALUES (1), (1), (2)) t (x)
Конечно, этот порядок может не совпадать с предполагаемым, поэтому этот обходной путь может применяться не ко всем входным данным!
Тот же подход можно использовать и для более сложных случаев (например, несколько списков для одной и той же группы), но итоговый запрос выглядит не так мило...
@MarkBarinstein: Конечно, такие простые запросы можно преобразовать по-разному, но если предположить, что фактический запрос намного сложнее, чем этот минимальный пример...
Не знаю почему, но я получаю SQL0214N, когда пытаюсь выполнить вышеуказанный запрос. Мне также нужно было привести x в список: ...listagg(DISTINCT CAST(x as VARCHAR), ',') WITHIN GROUP (ORDER BY CAST(x as VARCHAR)) ...
Пробовал на 11.5.7 и 11.5.8. Обратите внимание, когда он был представлен, но Db2 теперь содержит оператор CAST ::, похожий на postgres, который немного короче и читабельнее: ... listagg(DISTINCT x::VARCHAR, ',') WITHIN GROUP (ORDER BY x::VARCHAR)...
@Lennart-SlavaUkraini: Интересно, у Informix тоже есть такой синтаксис приведения, это, скорее всего, источник вдохновения, IMO. Я использую Db2 LUW из докера: hub.docker.com/r/ibmcom/db2 . Здесь также работает синтаксис: dbfiddle.uk/xRED-UsK
@LukasEder, я вытащил контейнер 11.5.8.0, и там он работает, но не работает, когда я пытаюсь использовать установленный 11.5.8.0, что странно. Я проведу расследование.
Также работает для вновь созданной базы данных на установленной версии 11.5.8, но не в перенесенной базе данных. Я мог бы открыть PMR по этому поводу.
@LukasEder, ошибка появляется, когда используется STRING_UNITS=CODEUNITS32 (db2 "UPDATE DB CFG FOR <db> USING STRING_UNITS CODEUNITS32"; db2stop force; db2start; db2 connect to ...)
.
Вы можете использовать DISTINCT в подзапросе в простом случае:
SELECT listagg (x, ',') WITHIN GROUP (ORDER BY x) FROM ( SELECT DISTINCT X AS X FROM (VALUES (1), (1), (2), (11)) t (x) )