RE: SQL0802 Type 12 using LISTAGG -- MIDRANGE-L

Thank you Birgitta!
That worked great!

I tried this before posting, but had the syntax incorrect.
Clob(ListAgg(mycolumn,...))

Thanks again!

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Friday, May 10, 2024 1:24 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL0802 Type 12 using LISTAGG

Did you try something like this (i.e. cast your column to be aggregated as large Object)

ListAgg(CLOB(YourColumn, 50), ', ')
Or
ListAgg(Cast(YourColumn as CLOB(50)), ', ')

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization - Education - Consulting on IBM i Database and Software Architect IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson) "Learning is experience . everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg Wilburn
Sent: Friday, 10 May 2024 17:41
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL0802 Type 12 using LISTAGG

I'm using LISTAGG to combine a char(64) field from an unknown number of rows into a single row with one large character field.

It works until I exceed the limit for a varying length field (I'm reading
4000 characters).

The source is a "notes" table in our legacy ERP. So I'm trying to present the notes in a large text field.
I'm attempting to insert the results into a new table containing a CLOB field/column.
Eventually, I would like to put a "new line" character after each row as well (if possible).

I cannot get past this error:
[SQL0802] Data conversion or data mapping error. Cause . . . . . : Error
type 12 has occurred.
12 -- Result of a concatenation operation on a varying-length field exceeded the maximum allowed length of the result type.

Suggestions welcomed.

Thanks,
Greg
[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.