Thanks Birgitta, yes this works. But the next step was going to be to email the physical file as an XLS and none of our email tools like CLOBS.
Ron Hudson | Programmer / Analyst
+1.704.752.6513 x1324
www.invue.com
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Saturday, January 25, 2020 2:03 AM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL LISTAGG() built-in function
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
--
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
