RE: Can one do a RUNSQL command with VALUES?

Short answer: Not directly!

What you could do (in CL):
1. Generate a View based on a SELECT Statement where your code is used, i.e.
something like this:
CHGVAR &CRTVIEW 'CREATE OR REPLACE VIEW QTEMP as (SELECT
GENERATE_SPREADSHEET(.....)...
RUNSQL &CRTVIEW ...
2. Declare the view as file
3. Read this view

... in either way I'd suggest to either write a RPG Program or an SQL Stored
procedure in which you'll generate the Spreadsheet and then call the RPG or
SQL Stored Procedure from your CL.

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 a4g
atl
Sent: Thursday, 11 July 2024 00:20
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Can one do a RUNSQL command with VALUES?

I need to do the following. Can it be done?

chgvar &wSqlCmd value('VALUES SYSTOOLS.GENERATE_SPREADSHEET('
+

*tcat 'PATH_NAME => ' +

*bcat '''/tmp/FLT855''' +

*tcat ',' +

*bcat 'SPREADSHEET_QUERY => ' +

*bcat '''select * from dppdata.flt855p2''' +

*tcat ',' +

*bcat 'SPREADSHEET_TYPE => ''xlsx'',' +

*bcat 'COLUMN_HEADINGS => ''LABEL'' )')

RUNSQL SQL(&wsqlcmd) COMMIT(*NONE) NAMING(*SQL)



chgvar &wSqlCmd value('VALUES SYSTOOLS.SEND_EMAIL(' +

*tcat 'TO_EMAIL => ''dfreinkel@xxxxxxxxxxxxxxxxxx'',' +

*bcat 'SUBJECT => ''Regenerated FLT855 as requested ''' +

*bcat 'concat (select host_name from
sysibmadm.env_sys_info),' +

*bcat 'BODY => ''Time extracted ''' +

*bcat 'concat current timestamp,' +

*bcat 'ATTACHMENT => ''/tmp/FLT855.xlsx'' )' )

RUNSQL SQL(&wsqlcmd) COMMIT(*NONE) NAMING(*SQL)



Can this be done?


Thank you.

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