Oracle XmlAgg function with case when-CodePudding

I have a query with joined tables where there are two columns that need to be compared and then need to be concatinated

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$

Unfortunately I am getting string concatenation is too long

so tried by adding.getClobVal() after EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg')

Unfortunately it is throwing inconsistent datatypes: expected CHAR got CLOB error

so tried by adding case statements inside xml attributes like below:

        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(),';')

script is executing with out errors but no data output. Is there a way to use xmlaggs with case when or nested?

CodePudding user response：

Finally It worked

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()