Re: performance optimization

[Ray Ontko <rayo@ONTKO.COM>]

John,

Is $LAST_RUN_DATE a variable which contains the run date?
If so, you should do something like this:

   let $where = 'Px.CHECK_DT=(SELECT MIN(AZZ.CHECK_DT) ' ||
                'FROM PS_PAY_CALENDAR AZZ ' }}
                'WHERE AZZ.CHECK_DT >= ' || $LAST_RUN_DATE || ')'
   LOAD-LOOKUP
   NAME= pays
   TABLE= 'ps_pay_calendar px'
   KEY= 'px.paygroup'
   RETURN_VALUE= ' CHAR(px.pay_begin_dt)||CHAR(px.pay_end_dt) '
   WHERE=$where

I think DB2 must be tripping over the $LAST_RUN_DATE in the
middle of the select.

Hope this helps.

Ray

> John Sayre@GAPINC
> 10/23/98 05:36 PM
> I would like to know how that is done with load lookup. This syntax,
>
> LOAD-LOOKUP
>  NAME= pays
>  TABLE= 'ps_pay_calendar px'
>  KEY= 'px.paygroup'
>  RETURN_VALUE= ' CHAR(px.pay_begin_dt)||CHAR(px.pay_end_dt) '
>  WHERE= 'Px.CHECK_DT=(SELECT MIN(AZZ.CHECK_DT) FROM PS_PAY_CALENDAR AZZ
> WHERE AZZ.CHECK_DT >= $LAST_RUN_DATE)'
>
>
> in DB2, gives an SQR error ,
>
>
> (SQR 5528) SQLBase SQLCOM error 6206 in cursor 2:
>    Column not in inserted table, updated table, or any table in FROM
>
> SQL: select distinct px.paygroup,
> CHAR(px.pay_begin_dt)||CHAR(px.pay_end_dt)
>      from ps_pay_calendar px where Px.CHECK_DT=(SELECT MIN(AZZ.CHECK_DT)
> FROM
>      PS_PAY_CALENDAR AZZ WHERE AZZ.CHECK_DT >= $LAST_RUN_DATE)
> Error at: select
>
> Error on line 265:
>    (SQR 3722) Couldn't set up cursor.
>
> SQRW: Program Aborting.
>
> although it runs fine from SQLtalk.
>
> I tried parentheses around the concat select  and also tried giving it
> acolumn alias. No luck.
>

----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/