Birgitta
Thank you.
I was hoping it could be done in CL so that I did not need another program.
I'll go ahead and make a SQLRPGLE utility program.
Regards

On Thu, Jul 11, 2024 at 1:54 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

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.


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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.