Re: How to retrieve SQL results into CL pgm

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I just saw the idea to use RTVMBRD against a table that is created from 
running the ASP_INFO table function.

I did wonder about using RTVMBRD against the view itself, so I tried it 
- it actually returned a value of 2 - not the number of rows in the 
result on the system I tried it on, where the SELECT in Run Sql Scripts 
has just 1 row.

Here's the CL code -
PGM
DCL        VAR(&CURRCDCNT) TYPE(*DEC) LEN(10 0)
RTVMBRD    FILE(QSYS2/ASP_INFO) NBRCURRCD(&CURRCDCNT)
ENDPGM

So I tried it over qsys2/sysfuncs - the CL gave me a record count of 
1696, while SELECT COUNT(*) FROM QSYS2.SYSFUNCS gave me 694.

Vaguely curious to think about, eh? But nothing I understand as I sit 
here! So back to the other post's suggestion, which should work.

Regards
Vern

On 6/3/2022 10:41 PM, Vern Hamberg via MIDRANGE-L wrote:
> Hi Steve
>
> You might have seen the thread today about SQL in CL. The usual way of 
> getting results in CL is to use SQL to create a table with the 
> results, then read that table that is specified in a DCLF.
>
> RUNSQL can't do a SELECT, but it can do a CREATE TABLE that has a 
> SELECT in it. Or if you need the table to exist already, and INSERT 
> statement can be used.
>
> I suspect there are several examples of this, it was mentioned in that 
> other thread as a common practice.
>
> Good luck!
> Vern
>
> On 6/3/2022 4:31 PM, Steve McKay wrote:
> > How can I run an SQL statement and retrieve the results into a CL pgm?
> >
> > For example, I want to SELECT COUNT(*) FROM QSYS2.ASP_INFO: but I 
> > want to
> > run it using RUNSQL in a CL program and get the COUNT into a variable in
> > the CL program.
> >
> > Something like this:
> > DCL &COUNT *DEC LEN(1)
> > RUNSQL SQL( SELECT COUNT(*) FROM QSYS2.ASP_INFO)
> > and get the SQL statement result in the CL &COUNT field.
> >
> > Ultimately, I want to do different things depending on whether there 
> > is 1
> > ASP or more than 1 ASP so I'm really after the count of ASPs on the
> > system.  If there is a better way to do this within CL, I'm open to
> > suggestions.
> >
> > Thanks,
> >
> > Steve McKay
> > (205) 585-8424
> > samckay1@xxxxxxxxx