I am trying to convert a static sql statement to allow for some flexibility and I'm having trouble with the dates.
This is the original SQL statement.
declare db2curs cursor for
integer(floor(:currentdate - getdatesql(TEMBDT)) /
10000) as age,
integer(floor(:currentdate - getdatesql(TEMHDT)) /
10000) as YOS
WHERE TEMEMP = TE1EMP and
TEMEES = 'FR'
Order by age desc;
Currentdate = %date(daterun:*USA0) ;
Getdatesql is a udf that converts an 8 digit date to a date field.
The problem I'm having is with current date.
Here is where I'm at now.
When FPEMP = 'F' ;
Sts = 'TEEMES = ' + Q + 'FR' + Q;
When FPEMP = 'P' ;
Sts = 'TEEMES = ' + Q + 'PT' + Q;
'select TE1DPT,TEMEMP,TEMNAM,TEMBDT,TEMHDT,TEMRDT,' +
'integer(floor(' + currentdate + ' - getdatesql(TEMBDT)) / ' +
'10000) as age, ' +
'integer(floor(' + currentdate + ' - getdatesql(TEMHDT)) / ' +
'10000) as YOS from TREMP,TREI1 ' +
'WHERE TEMEMP = TE1EMP and ' + STS + 'ORDER BY AGE,DESC';
Prepare SQLStatement from :SQLSTRING;
Declare db2curs cursor for SQLSTATEMENT ;
So currentdate which is a date, I can't get to be part of sqlstring
I feel like I'm missing the obvious here, but I can't get my brain around the solution.
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.