RE: Limiting size limits for LISTAGG function

On 12/31/2025 1:59 AM EST Birgitta Hauser <hauser@xxxxxxxxxxxxxxx> wrote:


The output of LISTAGG is either 4000 Byte (if the column in a Character
Column) or 1 MB if the column is a Large Object (LOB).
If you want the output shorter or any other length, just CAST the Result to
either CHAR/VARCHAR or Large Object and specify the maximum length.

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
Infodorado InfoDorado via MIDRANGE-L
Sent: Tuesday, 30 December 2025 19:05
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>;
smith5646midrange@xxxxxxxxx
Cc: Infodorado InfoDorado <infodorado@xxxxxxxxxxx>
Subject: Limiting size limits for LISTAGG function

Greetings fellow humans
I've checked around but can't find an answer to this.
How can you do a limit to the size of a LISTAGG result, say, to a column
width of 48?

I ran an SQL (complex) statement given to me to use to generate a output
file that will feed to a spreadsheet report. The SQL generates a database
file but one of the columns is LISTAGG(REFERENCE, ', ') WUTHIN GROUP (ODER
BY REFERENCE) AS MASTER_BOL but it generates a fixed column size of 4000.
Can I wrap all that inside a maximum size, truncated if necessary?

--Alan
--
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.