Re: trouble converting a sql statement

[Michael Schutte <mschutte369@xxxxxxxxx>]

Or the if you want to do the prepared route.

SQLString =  'select * from tremp, trei1 where TEMEMP = TE1EMP and temees =
?'

If FPEMP = 'F';
  value = 'FR';
ElseIf FPEMP = 'P';
  value = 'PT';
EndIf;


Exec SQL Prepare P1 From :SqlString;
Exec SQL DECLARE C1 CURSOR FOR P1;
Exec SQL Open C1 Using :value;



On Thu, Mar 7, 2013 at 4:14 PM, Michael Schutte <mschutte369@xxxxxxxxx>wrote:

> 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.