1.转成字符串,存储到数据库中
--B.DESCRIPT73,为需要转换的字段
--||'<br />'则代表在需要转换的字段后面拼接上一个换行符,存到数据库中
select F.POLICYNO AS POLICYNO ,
B.ENDORSENO AS ENDORSENO,
1 AS LINENO,
listagg(B.DESCRIPT73, '<br />') within GROUP(order by B.DESCRIPT73) AS DESCRIPT73
from PRPCANDPMAINALL F, PRPCANDPMAINALL B
where B.Endorseno is not null
and B.Policyno = F.Policyno
and F.Endorseno is null
AND B.DESCRIPT73 is not null
--and B.CREATEDATE >=DATE'${PAYREFDATE}'
group by F.POLICYNO, B.ENDORSENO
2.当字符串长度过大时,则需要转成clob存储在数据库中
select F.POLICYNO AS POLICYNO ,
B.ENDORSENO AS ENDORSENO,
1 AS LINENO,
--B.DESCRIPT73,为需要转换的字段
--||'<br />'则代表在需要转换的字段后面拼接上一个换行符,存到数据库中
xmlagg(xmlparse(content B.DESCRIPT73||'<br />' wellformed) order by B.DESCRIPT73).getclobval() AS DESCRIPT73
from PRPCANDPMAINALL F, PRPCANDPMAINALL B
where B.Endorseno is not null
and B.Policyno = F.Policyno
and F.Endorseno is null
AND B.DESCRIPT73 is not null
/*and B.CREATEDATE >=DATE'${PAYREFDATE}' */
group by F.POLICYNO, B.ENDORSENO