Re: trouble converting a sql statement -- MIDRANGE-L

On 07 Mar 2013 12:22, Smith, Mike wrote:

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
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 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.
Select ;
When FPEMP = 'F' ;
Sts = 'TEEMES = ' + Q + 'FR' + Q;
When FPEMP = 'P' ;
Sts = 'TEEMES = ' + Q + 'PT' + Q;
EndSl ;

sqlstring =
'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';

Exec sql
Prepare SQLStatement from :SQLSTRING;
Exec Sql
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.

If fully dynamic is desirable over use of parameter markers, then I would probably do the following:

Instead of building the entire predicate into the variable STS, I would leave the predicate visible in the formation\concatenation of the SQL statement; concatenating only the operand. So first setting STS:

Select ;
When FPEMP = 'F' ;
Sts = Q + 'FR' + Q ;
When FPEMP = 'P' ;
Sts = Q + 'PT' + Q ;
EndSl ;

As Scott noted in the first reply to the thread, and apparently what has already been done... the Currentdate as a Date data type variable must be cast to character to properly concatenate in the string. And as a SQL character constant embedded in a string, that value would also have to have apostrophes added. So any one of the following, where the first must be understood contextually to be a date literal, and the others explicitly state that the string is to be recognized as a date:

quotedDate = Q + %char(currentdate:*ISO) + Q ;

quotedDate = 'Date' + Q + %char(currentdate:*ISO) + Q ;

quotedDate = 'DATE(' + Q + %char(currentdate:*ISO) + Q + ')' ;

With that, I might do the following:

sqlstring =
' select' +
' TE1DPT,TEMEMP,TEMNAM,TEMBDT,TEMHDT,TEMRDT' +
' , integer(floor(' + quoteddate + ' - getdatesql(TEMBDT)) /' +
'10000) as age' +
' , integer(floor(' + quoteddate + ' - getdatesql(TEMHDT)) /' +
'10000) as YOS'
' from TREMP' +
' inner join TREI1' +
' on TEMEMP = TE1EMP'
' where TEMEES =' + STS +
' order by age desc'
;

However I would probably instead of using fully dynamic, make the SQL much simpler and use parameter markers for the variables that had their values just as easily established in the host language code. Michael had shown that, but only offering an example of the variable STS used in the WHERE clause, not of the other variable(s). So with parameter markers:

The variable STS need only be a 2-byte character to match the column TEMEES data type and length, and no more worry about adding the apostrophes:

Select ;
When FPEMP = 'F' ;
Sts = 'FR' ;
When FPEMP = 'P' ;
Sts = 'PT' ;
EndSl ;

The variable Currentdate can remain a date, keeping the original eval:

Currentdate = %date(daterun:*USA0) ;

Or the variable Currentdate could be a character string, but again no worry about adding apostrophes, using the following eval:

Currentdate = %char(%date(daterun:*USA0):*ISO) ;

With that, I might do the following with parameter markers; using the CAST to let the SQL know into what desired type is the host language variable to be cast:

sqlstring =
' select' +
' TE1DPT,TEMEMP,TEMNAM,TEMBDT,TEMHDT,TEMRDT' +
' , integer(floor( cast( ? as date) - getdatesql(TEMBDT)) /' +
'10000) as age' +
' , integer(floor( cast( ? as date) - getdatesql(TEMHDT)) /' +
'10000) as YOS'
' from TREMP' +
' inner join TREI1' +
' on TEMEMP = TE1EMP'
' where TEMEES = ?' +
' order by age desc'
;
Exec sql
Prepare SQLStatement from :SQLSTRING;
// check SQLCODE here
Exec Sql
Declare db2curs cursor for SQLSTATEMENT ;
Exec Sql
open C2 using :currentdate, :currentdate, :sts ;

FWiW: The ORDER BY is on an expression instead of a column which will [¿always?] require a temporary result for collation\sorting. Since that expression is derived from an effective date [the column TEMBDT, subtracted from a constant value], if that column is capable of being properly ordered [e.g. in the form YYYYMMDD] then that column likely could be sorted ASCending to effect the same result sequence; i.e. the oldest date will always yield the largest value for the expression and the newest date will always yield the smallest value for the expression.