Получение не группы по выражению при использовании функции listagg
CREATE TABLE source_det (
e_id NUMBER(10),
sys_name VARCHAR2(20),
ref_id NUMBER(10),
sys_other VARCHAR2(30)
);
INSERT INTO source_det VALUES(11,'SOURCE',992,null);
INSERT INTO source_det VALUES(11,'SOURCE',637,null);
INSERT INTO source_det VALUES(11,'SOURCE',null,'Manual');
INSERT INTO source_det VALUES(11,'TARGET',637,null);
commit;
Моя попытка:
SELECT e_id,
LISTAGG(source, ';') source,
LISTAGG(target, ';') target,
source_other
FROM (SELECT e_id,
CASE
WHEN sys_name = 'SOURCE' THEN
ref_id
END source,
CASE
WHEN sys_name = 'TARGET' THEN
ref_id
END target,
CASE
WHEN sys_name = 'SOURCE' AND ref_id IS NULL THEN
sys_other
END source_other
FROM source_det
GROUP BY e_id);
Из приведенного выше набора данных мне нужна только одна строка, которая должна быть возвращена из запроса SELECT, но вместо этого я получаю значения null вместе с требуемыми значениями.
Если sys_name является ИСТОЧНИКОМ, то результат должен быть ref_id. Если имеется более одной записи, то она должна быть разделена разделителем;
Если sys_name ЦЕЛЬ, то результат должен быть ref_id
Если sys_name является ИСТОЧНИКОМ, а ref_id равно нулю, то в результате должно получиться sys_other. Но из моего запроса я не получаю ни одной строки, а получаю 4 строки. Но в идеале мне нужен только один ряд с e_id 11
И самое главное, вместо нулевых значений может быть () вместо нулевого. Значит, надо исключить и это. После получения желаемого результата мы можем использовать DECODE(ref_id,'()',null) следующим образом.
Ожидаемый результат:
| e_id | источник | цель | источник_другое |
|---|---|---|---|
| 11 | 992;637 | 637 | Руководство по эксплуатации |
Используемый инструмент: SQL Developer (18c)
Ответы 1
Вам не нужен подзапрос, просто используйте его, как показано ниже.
SELECT e_id,
LISTAGG(CASE
WHEN sys_name = 'SOURCE' THEN
ref_id
END,
';') WITHIN GROUP(ORDER BY ref_id DESC) AS source,
LISTAGG(CASE
WHEN sys_name = 'TARGET' THEN
ref_id
END,
';') WITHIN GROUP(ORDER BY ref_id DESC) AS target,
LISTAGG(CASE
WHEN sys_name = 'SOURCE' AND ref_id IS NULL THEN
DECODE(sys_other,'()','',sys_other)
END,
';') WITHIN GROUP(ORDER BY 0) AS source_other
FROM source_det
GROUP BY e_id
где отсутствующий WITHIN GROUP (ORDER BY ..) должен следовать за выражением LISTAGG(...)
Спасибо за быстрый поворот. Это сработало для меня, но можем ли мы обработать () в нашем запросе, предположим, есть ли () вместо нуля в ref_id
Привет @Vicky, пожалуйста. Вы имеете в виду это..?
Да. Спасибо за это. И еще одно, как вы добавили демонстрационную кнопку скрипки в свой ответ ??
@Vicky, извините меня, что я недавно понял для кнопки «Демо», где я использую <kbd>[Demo](thelink)</kbd>
Все столбцы в вашем операторе SELECT должны быть частью агрегатной функции (например, LISTAGG) или включены в предложение GROUP BY. У вас есть неагрегированные столбцы, такие как e_id, но нет предложения GROUP BY.