Как присвоить столбцу значение результата LISTAGG?

Question

Пытаюсь присвоить значение, полученное функцией LISTAGG.

До этого на PL/SQL не писал, это попытка переделать код из PL/pgSQL:

for rec in cur loop
    update act_inspect set
        act_inspect.sanction = LISTAGG((
            select sanction from violation
            join act_inspect on (
                act_inspect.r_object_id = violation.document_id)
            where lower(violation.sanction) NOT IN (...)
            and violation.is_deleted = false 
            and violation.document_id = rec.r_object_id),' ; ')
    where current of cur;
end loop;

Получаю ошибку:

ORA-00934: групповая функция здесь не разрешена.

Поискал, везде примеры этой ошибки, когда LISTAGG находится в WHERE.

Answer 1

Допустим, есть такие две таблицы (на db<>fiddle):

select * from t1;

        ID VALS                            
---------- --------------------------------
         1                                 
         2                                 
         3                                 

select * from t2; 
  
        ID       T1ID VAL                                         
---------- ---------- --------------------------------------------
         1          1 val 1                                       
         2          1 val 2                                       
         3          1 val 3                                       
         4          2 val 4                                       
         5          2 val 5                                       
         6          2 val 6                                       
         7          3 val 7                                       
         8          3 val 8                                       
         9          3 val 9

В вопросе сценарий, получить из соединения этих двух таблиц список значенний столбца VAL и обновить столбец VALS результирующим списком.

Наиболее эффективно это можно сделать так:

declare
    cursor cur is
        select t1.rowid, listagg (t2.val,',') within group (order by t2.id) vals 
        from t1
        join t2 on t1id = t1.id
        group by t1.rowid;
    
    type currows is table of cur%rowtype;
    rows currows; 
begin 
    open cur;
    fetch cur bulk collect into rows;
     
    forall i in 1..rows.count
        update t1 set vals = rows(i).vals
        where t1.rowid = rows(i).rowid;
end;
/

Получим ожидаемый результат;

        ID VALS                            
---------- --------------------------------
         1 val 1,val 2,val 3               
         2 val 4,val 5,val 6               
         3 val 7,val 8,val 9

БЛОГ НА HUSL

Как присвоить столбцу значение результата LISTAGG?

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