Re: SQL - Generate Spreadsheet

Try

WITH X AS SELECT...
GENERATE FROM X

El mié, 2 de jul de 2025, 14:49, Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx> escribió:

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.