Re: SQL - Generate Spreadsheet

this one works in 1.1.9.8 ACS run sql scripts


VALUES
Systools.Generate_Spreadsheet(
Path_Name => '/tmp/query_spreadsheet',
SPREADSHEET_TYPE => 'xls',
Spreadsheet_Query => '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', Column_Headings => 'COLUMN');



Jim Oberholtzer wrote on 7/2/2025 2:37 PM:

What I need to do is generate the spreadsheets, I don’t need to consume the file in CL. The CL would only be a wrapper to run all the SQL statements needed. The preferred output would be a spreadsheet in the IFS.

So I’m still not clear on how to build the SQL so it will at least run in ACS.


--
Jim Oberholtzer
Agile Technology Architects

On Jul 2, 2025, at 1:10 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

But in CL you can neither directly receive return values from a scalar function nor execute a select statement.

The easiest work around is: Create an SQL View (in the QTEMP) based on the SELECT-Statement with the GENERATE_SPREADSHEET function.
Define the View with DCLF and then read though this file/view (as usual)

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 Jim Oberholtzer
Sent: Wednesday, 2 July 2025 19:49
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: 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://www.ermco-eci.com
https://ermcopower.com/
IBM i Award Winning Blog Site: 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://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.


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