Re: trouble converting a sql statement

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.

Inferred from the use of the RPG Select to setup the desired predicate for column TEEMES, the intent is apparently to enable selection by that column.

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 host variable Currentdate is used as a replacement value in the expressions of the select-list of the static SQL. Similarly, a host variable can be used as the operand for the equal-predicate in the WHERE clause.

Michael gave a similar reply, but using the SQL CASE [in place of the RPG Select] to effect the same variable selection. I tend to avoid the SQL CASE in a predicate of a query when I can set the value in the host language.

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 /* reformatted using JOIN syntax */
INNER JOIN trei1
on TEMEMP = TE1EMP
WHERE TEMEES = :Sts_2A /* or as an ANDed predicate in the ON */
Order by age desc
;

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 ;

<<SNIP trying to get currentdate into the dynamic stmt string>>

Just as Currentdate is set prior to the OPEN for the static SQL, the Sts_2A would need to be set. But instead of the escaped apostrophes to place the literal in the SQL statement string, the variable is declared the same data type as the TEEMES column [presumably CHAR(2)] and set to the desired value for selection [much like coded above]:

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