load-lookup question

[John Sayre <John_Sayre@GAP.COM>]

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.