Re: Can one do a RUNSQL command with VALUES?

[a4g atl <a4ginatl2@xxxxxxxxx>]

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.