Xml in postgresql

Oracle Code:

v_select_query := ‘select XMLAGG (XMLELEMENT (e, acct_group, ‘’,’‘).
EXTRACT (’‘//text()’‘)).getclobval ()
from (select distinct trim(ic_acct_group_702) acct_group
from investment_code,gl_skat,client_entity
where ic_acct_group_702 = trim(skat_group(+))
and CLIENT_ID_208 = ic_client_id_702(+)
and nvl(ic_active_flag_702, ‘‘A’’) <> ‘‘I’’
and ic_no_702 in (select ic_no_702 from investment_code
start with ic_no_702 in (select connection_ic_no_694 from personnel_ic_connector
where connection_pers_code_694 = ‘’’|| v_user_code ||‘’‘)
connect by prior ic_no_702 = ic_owner_no_702)’;

Migrated postgresql code:

ERROR: assignment source returned 3 columns

v_select_query:=’

SELECT RTRIM(string_agg(xpath(concat(‘‘rowset’’,‘’/c’‘, ‘’/text()’‘), XMLELEMENT
(NAME rowset, XMLAGG(XMLELEMENT (NAME e, CONCAT_WS(’‘, acct_group, ‘,’))))), ‘’)::XML::TEXT, ‘,’)
AS acct_group
(select distinct trim(ic_acct_group_702) acct_group
from investment_code left join gl_skat
on ic_acct_group_702 = trim(skat_group)
left join client_entity on
CLIENT_ID_208 = ic_client_id_702
and coalesce(ic_active_flag_702, ‘‘A’’) <> ‘‘I’’
and ic_no_702 in
(
WITH RECURSIVE hierarchy_cte AS (
SELECT
ic_no_702,
ic_owner_no_702
FROM investment_code
WHERE ic_no_702 IN (
SELECT connection_ic_no_694
FROM personnel_ic_connector
WHERE connection_pers_code_694 = ‘’’|| v_user_code ||‘’’
)
UNION ALL
SELECT
ic.ic_no_702,
ic.ic_owner_no_702
FROM hierarchy_cte hc
JOIN investment_code ic ON hc.ic_no_702 = ic.ic_owner_no_702
)
SELECT ic_no_702
FROM hierarchy_cte)';

Warum xmlagg? Was genau enthält die Spalte act_group? Was soll als Ausgabe erfolgen.

Ohne Beispieldaten und erwünschtes Ergebnis ist es praktisch unmöglich diese Frage zu beantworten.

1 „Gefällt mir“