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

[James Womeldorf <jwomeldo@fastenal.com>]

Hi Clark,

We use Oracle 8.
When I have to do a tricky load lookup I sometimes use syntax like this:

LET $WHERE =  'CUSTOMER_TYPE = ''' || $CUST_TYPE || ''''
LOAD-LOOKUP
NAME=MYCUSTREPORTLOOKUP
KEY=ROWNUM
RETURN_VALUE=CORPORATE_CUST_ID
TABLE='(SELECT ROWNUM, CORPORATE_CUST_ID, CUSTOMER_TYPE FROM PS_CUSTOMER
WHERE CUST_ID = CORPORATE_CUST_ID AND CUST_STATUS = ''A'')'
WHERE=$WHERE

Perhaps you can do something similar by including your entire gut SQL in the
TABLE definition.  (In my opinion it is WAY too difficult to get complicated
SQL shoehorned into a load lookup.  Sometimes I just give up and write a
view and use it as the TABLE)
Jim


-----Original Message-----
From: the dragon [mailto:ceprn@hotmail.com]
Sent: Friday, September 19, 2003 12:47 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] lookup and sub-select with effective date


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

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