Re: LOAD LOOK UP

[Ed Kelly <ed_kelly@HOTMAIL.COM>]

Fundamentally, everything looks OK.  However, the where clause that you
coded does not look right, i.e., you are not eliminating future dated rows.
Also, do you need to make this a local procedure?  Let's simplify it a bit:


begin-procedure load-lookup-tbls
LET $WHERE='D.DEPTID between ''A00000'' AND ''E99999'' and ' ||
           'D.EFFDT = (SELECT MAX(DD.EFFDT) ' ||
                      'FROM PS_DEPT_TBL DD ' ||
                      'WHERE DD.DEPTID=D.DEPTID and ' ||
                             DD.EFFDT <= ''' || $AsOfToday || ''''

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


Finally, for efficiency, you should probably use the ROWS argument.  If you
have a pretty good idea how many rows you will need, say 2000, then set
ROWS=2000 right before the WHERE argument.

Hope this helps,
Ed Kelly



_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com