Es liegt ein FEHLER vor: Die Zuweisungsquelle hat zwei Spalten zurückgegeben
Schwierigkeiten bei der Migration des XML-Elements nach Postgresql
oracle code:
v_select_query := 'select XMLAGG (XMLELEMENT
(e, acct_group, '','').EXTRACT (''//text()'')).getclobval ()
from (select distinct trim(iag7) acct_group
from investc,gl_skat,client_entity
where iag7 = trim(skat_group(+))
and ci2 = ic_client_id_702(+)
and in7 in
(select in7 from investc
start with in7 in (select connection_ic_no_694
from pic
where connection_pers_code_694 = '''|| v_user_code ||''')
connect by prior in7 = ion7)';
Postgresql code:
v_select_query := '
WITH RECURSIVE recursive_cte AS (
SELECT in7, ion7
FROM investc
WHERE in7 IN (
SELECT connection_ic_no_694
FROM pic
WHERE connection_pers_code_694 = '|| v_user_code ||'
)
UNION
SELECT ic.investment_code_no, ic.ion7
FROM investc ic
JOIN recursive_cte rc ON ic.in7 = rc.ion7
)
SELECT string_agg(acct_group, '') AS xml_result
FROM (
SELECT DISTINCT TRIM(ic.ic_acct_group_702) AS acct_group
FROM investc ic
LEFT JOIN gl_skat gs ON ic.ic_acct_group_702 = TRIM(gs.skat_group)
LEFT JOIN client_entity ce ON ce.ci2 = ic.ic_client_id_702
WHERE ic.in7 IN (
SELECT in7
FROM recursive_cte
)
)' AS subquery;`Vorformatierter Text`