Re: SQL - Generate Spreadsheet

Technically, you cannot use VALUES in a CL ...but... you can if you shell out to the db2 utility in the IFS.

Here is an example of using the SysTools.Generate_Spreadsheet function in a CL program:

Pgm

Dcl Var(&Cmd) Type(*Char) Len(500)
Dcl Var(&Results) Type(*Char) Len(50)


/* -------------------------------------------------------------------------- */
/* Make Some Magic Happen */
/* -------------------------------------------------------------------------- */

ChgVar Var(&Cmd) +
Value('db2 +
"Values SysTools.Generate_Spreadsheet( +
Path_Name => ''/usr/query_spreadsheet'', +
Spreadsheet_Query => ''Select * +
From Orders +
Where Shipped = ''YES'' '',+
Column_Headings => ''COLUMN'') " +
| datarea -w /qsys.lib/usrlib.lib/usrdta.dtaara')

QSh Cmd(&Cmd)
MonMsg MsgId(QSH0000)

RtvDtaAra DtaAra(UsrLib/UsrDta (1 50)) RtnVar(&Results)

SndUsrMsg Msg(&Results) MsgType(*Info)

/* -------------------------------------------------------------------------- */
/* All Done */
/* -------------------------------------------------------------------------- */

EndPgm



Thanks,



Herb.


________________________________
From: Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
Sent: Wednesday, July 2, 2025 1:49 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>; Michael Mayer <michael.mayer@xxxxxxxxxxxxx>
Subject: Re: SQL - Generate Spreadsheet

Yes I have that example however ACS rejects it with VALUES not allowed there. So the question is how to build the SQL with it so it’s accepted?


Jim Oberholtzer
Agile Technology Architects

On Jul 2, 2025, at 12:03 PM, Michael Mayer via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Found this example:

VALUES SYSTOOLS.GENERATE_SPREADSHEET(
PATH_NAME => '/usr/query_spreadsheet',
SPREADSHEET_QUERY => 'select order_number, customer, balance from orders where shipped = ''YES''',
COLUMN_HEADINGS => 'COLUMN');

Respectfully,
Michael Mayer
IBM i on Power System Admin
ERMCO-ECI
2225 Industrial Rd
Dyersburg, Tennessee 38024
Cell, Office and Text Messages: 518.641.8906
Email: michael.mayer@xxxxxxxxxxxxx
https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.ermco-eci.com%2F&data=05%7C02%7Cherbr%40masonassociates.com%7Cbef51e4360424078b22508ddb990c6bb%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C638870754360976443%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C64000%7C%7C%7C&sdata=ZXLt%2BtEblehzi%2Fg6fGjcNnb5cIBjTfpSXUUoWdb6vhk%3D&reserved=0<https://www.ermco-eci.com/>
https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fermcopower.com%2F&data=05%7C02%7Cherbr%40masonassociates.com%7Cbef51e4360424078b22508ddb990c6bb%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C638870754361006282%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C64000%7C%7C%7C&sdata=uRdSzyBSC5CZcZRmcIOPNuZYxKQIi1fVV9Dml17J1h0%3D&reserved=0<https://ermcopower.com/>
IBM i Award Winning Blog Site: https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fibmireference.blogspot.com%2F&data=05%7C02%7Cherbr%40masonassociates.com%7Cbef51e4360424078b22508ddb990c6bb%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C638870754361023910%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C64000%7C%7C%7C&sdata=4YhisitluwxWc1cK3R9VH6fPEKwO4fkrFw9LNwonMGk%3D&reserved=0<https://ibmireference.blogspot.com/>
Awarded IBM 2024/2025 Advocate, Contributor, Influencer and IBM i Ready Badges.


2. SQL, generate spreadsheet (Jim Oberholtzer)

----------------------------------------------------------------------

message: 2
date: Wed, 2 Jul 2025 11:08:16 -0500
from: Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
subject: SQL, generate spreadsheet

I have this SQL that I stole from Simon Hutchinson, (altered to include what I need) but the last bit is to generate a spreadsheet from the output.

I see the generate spreadsheet scalar function, but the documentation is not clear or my SQL stupidity is coming out. How do I set this up to generate a spreadsheet when run from an CLLE program?

SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT,
OBJTYPE,USER_NAME,OBJ_AUTH,
OBJOPER AS OPR,OBJMGT AS MGT,OBJEXIST AS EXIST,
OBJALTER AS ALTER,OBJREF AS REF,
DATA_READ AS READ,DATA_ADD AS ADD,DATA_UPD AS UPDATE,
DATA_DEL AS DELETE,DATA_EXEC AS EXECUTE
FROM QSYS2.OBJECT_PRIVILEGES
WHERE SYS_DNAME = 'QSYS'
AND OBJTYPE = '*CMD'
ORDER BY SYS_ONAME,USER_NAME


--
Jim Oberholtzer
Agile Technology Architects






------------------------------

--
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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cbef51e4360424078b22508ddb990c6bb%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C638870754361042867%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C64000%7C%7C%7C&sdata=XHmJXLhXnOPBwBBfGlroiuVEvDtbmA1NB2EvO4WWNVg%3D&reserved=0<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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cbef51e4360424078b22508ddb990c6bb%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C638870754361056670%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C64000%7C%7C%7C&sdata=xaO8pjzenqMcRInL8SpPzO3d9QZuChxVC0gigrdb%2BGY%3D&reserved=0<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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cbef51e4360424078b22508ddb990c6bb%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C638870754361069756%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C64000%7C%7C%7C&sdata=rrzbCsRMyRh%2BppzxPpvOEOXN8x3Q1bKp6YigrmqFgTM%3D&reserved=0<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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7Cherbr%40masonassociates.com%7Cbef51e4360424078b22508ddb990c6bb%7C93f29ae1ee21447692c5e2f250e4284e%7C0%7C0%7C638870754361083243%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C64000%7C%7C%7C&sdata=ZWXhJpy5niVfjkPYwG99oM2n6Mc0Grq1XlvdcOKcJUM%3D&reserved=0<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.