Re: How to retrieve SQL results into CL pgm

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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