On 7/30/2013 6:25 PM, A Paul wrote:
Note: I lost this CLP SQL thread because it was a reply to 'LEAVE
outside of a DO group' and that delayed my response :-(
I am looking to do date comparison in CL program.
I get the date from a flat file with the below SQL
SELECT
substr(admadmrec, 9,8)
from admfile
the value is in format yyyymmdd.
How do you output this date into a variable (&VAR) to compare with another variable(&DATE) that picks APPLICATION DATE in the same format 20130608(yyyymmdd).
Too much going on in CL in my opinion. CL should be doing job
control-ish stuff, not reading the database and making business decisions.
Having said that, Rexx is a fairly simple way to get where you want to
go, and it's already loaded on your machine so there's nothing new to buy.
Given this CLP:
pgm
dcl &date *char 10
dcl &len *int (4) value(10)
dcl &rc *int (2)
/* execute the SQL statement */
strrexprc sqltest2 srcfile(buck/qrexsrc)
call QREXQ ('P' &date &len 0 &rc)
dmpclpgm
endpgm
and this Rexx source member:
/* SQLTEST2 */
/* example of using SQL to pass a value back to a CL program */
address '*EXECSQL'
PRVDATE = '0001-01-01'
sql_Stmt = ,
'select CHAR(DATE(CURRENT_DATE - 15 DAYS)) ',
' from sysibm/sysdummy1'
'execsql SET OPTION DATFMT = *ISO '
'execsql PREPARE S2 FROM :sql_Stmt '
'execsql DECLARE C1 CURSOR FOR S2'
'execsql OPEN C1 '
'execsql FETCH C1 INTO :PRVDATE'
'execsql CLOSE C1'
/* put the value on the external queue */
/* This will allow the calling CLP to retrieve it via */
/* CALL QREXQ PARM('P' &DATE &LEN 0 &RC) */
queue (PRVDATE)
exit
You will be able to call the CLP which will execute the Rexx program
which will run the SQL and send the variable to the Rexx stack. Then
the CLP can call QREXQ to pull the value(s) off the stack and into CL
variables.
This is only a template, not the exact solution to your problem. You'll
need to format the date in whatever form you need it, either in Rexx or
CL. You should also read up on Rexx error handling: SIGNAL ON ERROR.
--buck
midrange.com
