Re: LOAD LOOK UP

[Sam Spritzer <SSpritzer@GW.CTG.COM>]

John...
Code your effective date logic directly into the LOAD LOOKUP at the WHERE = line.
Sam


<<< John Sayre <John_Sayre@GAP.COM>  8/24  2:00p >>>
From: John Sayre@GAPINC on 08/24/99 11:00 AM
I am attempting to use loadlookup.  Because the table is effective dated
and I only want to get the highest effective dated row, I am trying to plug in a
where clause.

If I don't set $where to '', I get an error that the where clause ($where)  has
not been prepared.
If I do, the where does not get compiled & does not get executed

 begin-procedure load-lookup-tbls($WHERE)

 LET $WHERE=''

 let $where='d.deptid between '||''''||'A00000'||''''||' AND '
||''''||'E99999'||''''
 LET $WHERE = $WHERE || ' AND D.EFFDT = (SELECT MAX(DD.EFFDT) FROM PS_DEPT_TBL
DD '
 LET $WHERE = $WHERE || ' WHERE D.DEPTID=DD.DEPTID)'

 SHOW 'LOAD LOOKUP ' $WHERE

 LOAD-LOOKUP  NAME= depts

              TABLE= 'ps_dept_tbl d'

              KEY= 'd.deptid'

              RETURN_VALUE= 'd.descr'

              WHERE= $WHERE

 SHOW 'LOAD LOOKUP DONE'

 END-PROCEDURE

If I

When I run the sqr with -s flag, I get dup rows and the SQL gets complied as

 Cursor #18:
   SQL = select distinct d.deptid, d.descr from ps_dept_tbl d order by 1, 2
 Compiles = 2
 Executes = 1
 Rows     = 7186

What needs done?