Re: trouble converting a sql statement

[Michael Schutte <mschutte369@xxxxxxxxx>]

You could have just added this to the original.


       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
-- new where clause.
          TEMEES =  case :FPEMP  when 'F' Then 'FR' when 'P' then 'PT' END
--

          Order by age desc

On Thu, Mar 7, 2013 at 3:42 PM, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx>wrote:

> Add a space....
>                                          /space before your order by
> clause...
>   'WHERE TEMEMP = TE1EMP and ' + STS + ' ORDER BY AGE,DESC';
>
> Hth,
> -Eric DeLong
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Mike
> Sent: Thursday, March 07, 2013 2:23 PM
> To: Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx)
> Subject: trouble converting a sql statement
>
> 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.
>
>
> Thanks
> Mike
> 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.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.