[sqr-users] lookup and sub-select with effective date

["the dragon" <ceprn@hotmail.com>]

Hi all,

I was just at the site doing a search, and, while I didn't read all 449 
hits, the closest I could come to answering my question is this post : 
http://www.sqrug.org/sqr-users/sqrusers_98q3/msg01074.html

I am trying to load a lookup with the most current accounts from 
ps_gl_account_tbl, and I am unable to figutre out how to use effective 
dating, since you have to supply the keys in the sub-select, which means the 
referenced table outside the sub-select has to have an alias....

example :
load-lookup
name=DEPARTMENT
rows=50
table=DEPARTMENT_TBL
key=DEPTID
return_value=DEPTNAME
where=' A.EFFDT =  (SELECT MAX(EFFDT) FROM PS_DEPARTMENT_TBL
        WHERE A.SETID = SETID
          AND A.DEPTID = DEPTID
          AND EFFDT <= SYSDATE)'

notice in this example the sub-select references the alias A from the main 
select

any ideas would be appreciated.

thank you in advance,

clark

PSA: Salary <> Slavery.  If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul.  Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent.  Be careful lest you let other people 
spend it for you."

Carl Sandburg
(1878 - 1967)

_________________________________________________________________
Need more e-mail storage? Get 10MB with Hotmail Extra Storage.   
http://join.msn.com/?PAGE=features/es

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users