What if you try to cast the LISTAGG to a CLOB
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"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)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Hudson,
Ron
Sent: Freitag, 24. Januar 2020 21:16
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL LISTAGG() built-in function
I have a SQL statement that works great using the LISTAGG() built in
function.
But once I wrap the SQL with "Create table QTEMP/fileA as ( " ....... ")
with data" to create a work file, the field from the LISTAGG becomes blanks.
No sql errors, all other fields from the select statement are in the qtemp
file.
Working SQL statement:
Select b.vqvend, b.vqocde, b.VQREV, b.VQQPRV, VQLIN, VQNTYP,
b.VQCRTU, min(b.VQCRTZ) AS CRTDATE,
listagg(cast(b.VQNOTE as char(150)) on overflow truncate) as notes
From RQ009PF b Join RQ001PF a on b.vqvend = a.vqvend and
b.vqocde = a.vqocde and b.vqrev = a.vqrev and b.vqqprv = a.vqqprv
Where a.VQDATE >= '2020-01-21' And a.VQDATE <= '2020-01-21'
Group by b.VQVEND, b.VQOCDE, b.VQREV, b.VQQPRV, VQLIN, VQNTYP, b.VQCRTU
Order by b.VQVEND, b.VQOCDE, b.VQREV, b.VQQPRV, VQLIN, VQNTYP, b.VQCRTU
Failing SQL statement:
Create table QTEMP/Fila as (
Select b.vqvend, b.vqocde, b.VQREV, b.VQQPRV, VQLIN, VQNTYP,
b.VQCRTU, min(b.VQCRTZ) AS CRTDATE,
listagg(cast(b.VQNOTE as char(150)) on overflow truncate) as notes
From RQ009PF b Join RQ001PF a on b.vqvend = a.vqvend and
b.vqocde = a.vqocde and b.vqrev = a.vqrev and b.vqqprv = a.vqqprv
Where a.VQDATE >= '2020-01-21' And a.VQDATE <= '2020-01-21'
Group by b.VQVEND, b.VQOCDE, b.VQREV, b.VQQPRV, VQLIN, VQNTYP, b.VQCRTU
Order by b.VQVEND, b.VQOCDE, b.VQREV, b.VQQPRV, VQLIN, VQNTYP, b.VQCRTU
) with data
The file is created but the notes field is blanks.
Casting to the maximum size "cast(listagg(cast(b.VQNOTE as char(150))) AS
char(1050)) as notes" didn't help either.
Any ideas?
Thanks
Ron
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
