Как сделать так, чтобы LISTAGG учитывала NULL значения?

Question

Есть таблица TABLE1 с такими данными:

+------------+
|    COL1    |
+------------+
|   FOO      |
|   BAR      |
|  (null)    |
|   EXP      |
+------------+

Если выполняю:

SELECT listagg(col1, '#') within group(ORDER BY rownum) 
  FROM table1;

COLLIST
----------------
FOO#BAR#EXP

Функция LISTAGG игнорировала NULL значения, но хотел бы их учесть, т.е. получить: FOO#BAR##EXP.

Как можно этого достичь, чтобы не писать свою функцию?

_{Свободный перевод вопроса Oracle 11g: LISTAGG ignores NULL values от участника @WBAR}

Answer 1

Самое простое, добавить разделитель к самому значению:

select rtrim (listagg (col1||'#~') within group (order by null), '#~') collist 
from table1
/

COLLIST
------------------------
FOO#~BAR#~#~EXP

Возможно также с XMLAGG. Хотя на больших сетах данных будет не так эффективно, но не будет ограничения на макс. длину символьной строки:

select rtrim (xmlagg (xmlelement (e, col1, '#~'))
    .extract ('//text()').getClobVal (), '#~') collist 
from table1
/

COLLIST
------------------------
FOO#~BAR#~#~EXP

Answer 2

Вся проблема - из того, что Oracle не различает пустую строку и NULL, для него это одно и то же (причём с числовыми типами ничего подобного нет). Поэтому простое и очевидное

SELECT listagg(COALESCE(col1, ''), '#') within group(ORDER BY rownum) 
  FROM table1;

сработает где угодно - но не в Oracle.

Возможный выход - замена NULL на строковый литерал, который гарантированно отсутствует в значениях. В 99% случаев в качестве такого литерала я предпочитаю использовать CHR(0). После замены агрегирование будет выполнено нормально, без игнорирования NULL, а после агрегирования нужно выполнить обратную замену. Т.е.

SELECT REPLACE(listagg(COALESCE(col1,CHR(0)), '#') within group(ORDER BY rownum), CHR(0), '') 
  FROM table1;

Если же агрегируемое поле имеет бинарный тип (RAW/LOB), то задача выбора правильного "заменителя NULL" усложняется... впрочем, о данных что-нибудь, да известно, так что возможный заменитель (который не обязан быть, кстати, однобайтовым) вполне можно предсказать. Да и необходимость подобной конкатенации для бинарных данных - вещь весьма сомнительная.

БЛОГ НА HUSL

Как сделать так, чтобы LISTAGG учитывала NULL значения?

Ответы (2 шт):