Oracle XmlAgg函数，大小写为

Question

我有一个带有联接表的查询，其中有两列需要进行比较，然后需要合并

select tc.id$ as id, case
when a.comment is null and b.comment is null then ''
when a.comment is null and b.comment is not null then RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES ('Updated from null -> ' || b.comment || ';' AS "Seg"))ORDER BY b.comment ASC).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),';')
when a.comment is not null and b.comment is null then RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES ('Updated to null from || ' a.comment || ';' AS "Seg"))ORDER BY a.comment ASC).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),';')
when a.comment is not null and b.comment is not null and a.comment <> b.comment then
RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES ('Updated from '|| a.comment || '->' || b.comment ||';' AS "Seg"))ORDER BY a.comment ASC).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),';')
else ''end as TESTER_COMMENT_UPDATED from tableA tc left join htableA a on a.id$=tc.id$ left join htableA b on b.id$=tc.id$group by tc.id$

不幸的是，我得到的字符串串联太长

所以尝试在EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg')之后添加.getClobVal((

不幸的是，它抛出了不一致的数据类型：预期的CHAR得到了CLOB错误

因此尝试在xml属性中添加case语句，如下所示：

RTRIM (XMLAGG (XMLELEMENT (E,XMLATTRIBUTES (case 
when a.comment is not null and b.comment is not null and a.comment <> b.comment then
'Updated from ' || a.comment || '->' || b.comment when a.comment is null and b.comment is not null then 'Updated from null -> ' || b.comment
when a.comment is not null and b.comment is null then 'Updated to null from ' || a.comment
else '' end as seg
))).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg').getClobVal(),';')

脚本执行时没有错误，但没有数据输出。有没有一种方法可以将xmlaggs与case-when或nested一起使用？

Answer 1

最后成功了

when a.comment is null and b.comment is null then null
when a.comment is null and b.comment is not null then  
XMLELEMENT (E,
XMLELEMENT(c1, 'Updated from '''' to: '),
XMLELEMENT(c2, b.comment)
).extract('//text()').getClobVal()  
when a.comment is not null and b.comment is null then 
XMLELEMENT (E,
XMLELEMENT(c1, 'Updated to '''' from: '),
XMLELEMENT(c2, a.comment)
).extract('//text()').getClobVal() 
when a.comment is not null and b.comment is not null and a.comment <> b.comment then 
XMLELEMENT (E,
XMLELEMENT(s, 'Updated from: ' ||CHR(13)),
XMLELEMENT(c1, a.comment),
XMLELEMENT(sep, CHR(13) ||' to: ' || CHR(13)),
XMLELEMENT(c2, b.comment)
).extract('//text()').getClobVal()