Jblue R
asked on
listagg fail -string too long (4000) need to convert to XML
This code is a small portion of a view creation that develops a perfect answer except that one track_thing_id/thing_id combination exceeds the 4000 byte listagg limitation. Because that one combination exceeds the 4000 character/byte limitation of listagg, I believe I need an XML conversion.
So what I need from the above code is the XML equivalent.. and I am hoping the above is sufficient for a resident expert to return the needed SQL.. And would gladly consider any alternative solution to the listagg limitation.
All pointers and suggestions appreciated .. I have tested, and yes, there is only one combination (out of a large number) that come anywhere close to the 4000 byte limitation, and it clearly does exceed.. It is entirely possible that future data entry could cause additional track_thing_id/thing_id combinations to exceed the limitation.
left outer join
( select mi8.track_thing_id,
listagg (
nvl (p.cipher, '')
|| '-'
|| mip.kellogg_nur
|| '-'
|| nvl (pls.situation, 'Nsitn'),
', ')
within group (order by p.cipher)
as "thing/sit"
from ml_track_thing mi8
join ml_track_thing_stuff mip
on mi8.track_thing_id = mip.track_thing_id
left outer join stuffstuff p on p.id = mip.endpr_id
left outer join lic_endpr_permit pl
on ( pl.endpr_id = mip.endpr_id
and pl.house_id = mi8.place_id)
left outer join lic_endpr_permit_situation pls
on pl.permit_situation_id = pls.id
group by mi8.track_thing_id
order by mi8.track_thing_id) spcl
So what I need from the above code is the XML equivalent.. and I am hoping the above is sufficient for a resident expert to return the needed SQL.. And would gladly consider any alternative solution to the listagg limitation.
All pointers and suggestions appreciated .. I have tested, and yes, there is only one combination (out of a large number) that come anywhere close to the 4000 byte limitation, and it clearly does exceed.. It is entirely possible that future data entry could cause additional track_thing_id/thing_id combinations to exceed the limitation.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@slightwv-- thank you for your post. I have to keep all the aliases intact to get it back in to the view statement. I am trying to work from yours back in to reality. Thank you !
ASKER
Got it!! Perfect.. thank you. I have the view.. the view is returning the solution field as HUGECLOB, which may become another issue...for another day... .. You immediately returned the exact answer for which I searched..
Thank you once more.
Thank you once more.
https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?anchorAnswerId=25864822#a25864822
If you would like a copy/paste solution that wouldn't be a complete guess, please post sample data with the expected results.