Re: load-lookup question

[Randy Learn <rlearn@UAKRON.EDU>]

John

The $LAST_RUN_DATE is the problem because once it is in quotes it does not
get interperated.  I just reciently ran into this problem in DB2 and the
following is how I fixed it.

Try ... example of your last line of code..

WHERE AZZ.CHECK_DT >= '  || '''  || $LAST_RUN_DATE || ')''

John Sayre wrote:

> John Sayre@GAPINC
> 10/26/98 03:54 PM
>
> 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
> a column alias. No luck.
>
> I also tried to put the where clause in a text variable, which I have
> tried before with the same bad results. I always get an error which says
> 'Variable not defined '. How do I get around that ?
>
> I hard coded a date in the where, and this worked, so I now know that
> the problem is the where.