I've written the below statement which returns the data in the format i need to update another table with however i'm struggling with the update
SELECT element_id,
LISTAGG(cast(0 as varchar2(20))||', '|| VALUE, ' | ') WITHIN GROUP (ORDER BY display_order)
FROM EDRN.MD$$_ELEMENT_VALUES
WHERE element_id IN
(SELECT element_id FROM EDRN_NEW.DATA_DICTIONARY)
GROUP BY element_id;
I done a basic convert into an UPDATE statement
UPDATE EDRN_NEW.DATA_DICTIONARY
SET Choices = (LISTAGG(CAST(0 AS VARCHAR2(20))||', '|| VALUE, ' | ') WITHIN GROUP (ORDER BY display_order)
FROM EDRN.MD$$_ELEMENT_VALUES
WHERE element_id IN
(SELECT element_id FROM EDRN_NEW.DATA_DICTIONARY)
GROUP BY element_id);
This received a "ORA-00934: group function is not allowed here" error. I'm unsure how to remove the group function but retain the data format i require?
You need a subquery to use listagg()
. In this case, a correlated subquery:
update EDRN_NEW.DATA_DICTIONARY dd
set choices = (SELECT LISTAGG(cast(0 as varchar2(20))||', '|| VALUE, ' | ') WITHIN GROUP (ORDER BY display_order)
FROM EDRN.MD$$_ELEMENT_VALUES ev
WHERE ev.element_id = dd.element_id
)
where exists (select 1
from EDRN.MD$$_ELEMENT_VALUES ev
where ev.element_id = dd.element_id
);