run SQL in CL using LOOKUP command

[64waves]

Good afternoon, trying to find the details for the LOOKUP command described in this article:

http://www.mcpressonline.com/programming/sql/using-dynamic-sql-in-cl-part-2retrieving-a-data-value

I want to set this up and try and use it but the links no longer work.

I also found information for a RUNSQLLE command, but I don't think I can return a value in that command. I just want to get a Count(*) on something.

Any help would be appreciated. Thanks.

[TedHolt]

There is a "Contact us" link at the bottom of that page. I would try that.

If there is no WHERE clause in your SQL statement, you might be to use the RTVMBRD command instead. The NBRCURRCD parameter gives you the number of records in a file member.

If there is a WHERE clause, you can use the usual method -- call an RPG program that executes the SQL command and passes the count back to the CL program thru a parameter.

[64waves]

Hello Ted, I was looking for a better way for a colleague here that is a Cobol programmer and is not real versed in RPG. I think he is trying to get a count(*) for certain criteria. I set up a STRQMQRY command that does a select count(*) to a work file on the system, then RCVF in the CL to read the count value from the file. I wanted to see if those other options would work, I will try to contact the author of that link. If I don't get anywhere with those other commands, I will see if he is familiar with STRQMQRY.

Thanks!

[Brian Rusch]

If you are going to create a work table in QTEMP, then instead of QMQRY you could just run the RUNSQL command in the CL program to create the work file. An example of an SQL statement to get a count of records would be:

Create table qtemp/count as (select count(*) as reccnt
from library/filename where field = 'something') with data

[64waves]

Thanks Brian, how do you get the value of RECNT after this statement is executed? I am trying this and I get 'File not previously declared':

PGM

DCL VAR(&SUBJECT) TYPE(*CHAR) LEN(70)
DCL VAR(&ICOUNT) TYPE(*char) LEN(5)

DCLF QTEMP/COUNT
RUNSQL SQL('Create table qtemp/count as (select +
count(*) as reccnt from systslibrf/gathst +
where credit = ''V'') with data')


RCVF
CHGVAR VAR(&ICOUNT) VALUE(&reccnt)
CHGVAR VAR(&SUBJECT) VALUE('Count is ' *CAT +
&ICount )
SNDPGMMSG MSG(&subject)


RETURN
ENDPGM

[TedHolt]

I was looking for a better way for a colleague here that is a Cobol programmer and is not real versed in RPG. I think he is trying to get a count(*) for certain criteria.

He can write the program in COBOL instead of RPG. It works the same way.

[TedHolt]

I get 'File not previously declared'

The file has to exist at compile time, 64, so you will have to create it outside of the CL program. At run time, you can clear the file member, load it with SQL, and read it with RCVF.

DCLF   COUNTER  OPNID(C)

CLRPFM COUNTER
RUNSQL SQL('insert into counter (select + 
count(*) as reccnt from systslibrf/gathst +
where credit = ''V'')') 
RCVF  OPNID(C)

[64waves]

Hello Ted, I showed that to my colleague, and he is doing pretty much the same thing with RUNSQL. I don't want to step on toes and ask why he is not doing it in Cobol, I was just trying to help him with his desire to do it cleaner.

The LOOKUP example looks pretty sweet, just what I want, and the article is from 2003, it must be out there somewhere.

DCL VAR(&NO_ORDERSA) TYPE(*CHAR) LEN(128)
DCL VAR(&NO_ORDERS) TYPE(*DEC) LEN(10 0)

LOOKUP LOOKUPEXP('COUNT(*)') TABLE(GATHST) +
WHERE('DATEIN=Current_Date') +
RETURNVAR(&NO_ORDERSA)

Thanks for all the input.

[TedHolt]

The LOOKUP example looks pretty sweet

That's because Mike Sansoterra wrote it.

[tomliotta]

One potential problem with finding LOOKUP is that it's old by today's standards. The world of CL changed way back when V5R3 arrived and grew much more in V5R4 and i 6.1. Once V5R4 was established, CL could run SQL statements by itself by calling the SQL CLI (essentially ODBC) APIs. The SELECT statement can return the COUNT() by itself and CL can run the SELECT. Not trivial, but even experience with ODBC in Visual Basic can be enough.

But it only takes a single SELECT INTO statement and a variable definition to get the COUNT() in COBOL. (Well, plus the DIVISION & SECTION statements, etc.) Why try to do it in CL at all?