Peoplesoft sub-query of a sub-query.
- From: Roy Mercer <roy.mercer@xxxxxxxxx>
- Date: Mon, 27 Aug 2007 17:19:16 -0000
AIX 5.3 IDS 9.40.FC5
How can this be re-written to eliminate the subquery?
I can't seem to get MAX to work with the outer join on ()
select x0.setid ,x0.cust_id ,x0.name1 ,x0.roleuser ,
sum(x1.bal_amt) ,x2.cr_limit ,x2.cr_limit_rev_dt ,x2.cr_limit_range ,
x2.credit_class ,x0.currency_cd
from "sysadm".ps_customer x0 ,"sysadm".ps_cust_data
x1 ,"sysadm".ps_cust_credit x2 where ((((((((x0.cust_status
= 'A' ) AND (x0.bill_to_flg = 'Y' ) ) AND (x0.cust_level !=
'P' ) ) AND (x0.setid =
(select x3.setid from
"sysadm".ps_set_cntrl_rec
x3 where ((x3.setcntrlvalue = x1.business_unit ) AND (x3.recname
= 'CUSTOMER' ) ) ) ) ) AND (x0.cust_id = x1.cust_id ) ) AND
(x2.setid = x0.setid ) ) AND (x1.cust_id = x2.cust_id ) )
AND (x2.effdt =
(select max(x4.effdt ) from "sysadm".ps_cust_credit
x4 where ((((x4.setid = x2.setid ) AND (x4.cust_id = x2.cust_id
) ) AND (x4.effdt <= TODAY ) ) AND (x4.eff_status = 'A' )
) ) ) )
group by x0.setid ,x0.cust_id ,x0.name1 ,x0.roleuser
,x2.cr_limit ,x2.cr_limit_rev_dt ,x2.cr_limit_range ,x2.credit_class
,x0.currency_cd ;
.
- Follow-Ups:
- Re: Peoplesoft sub-query of a sub-query.
- From: Art S. Kagel
- Re: Peoplesoft sub-query of a sub-query.
- Prev by Date: Re: oncheck -cD errors
- Next by Date: Re: Peoplesoft sub-query of a sub-query.
- Previous by thread: oncheck -cD errors
- Next by thread: Re: Peoplesoft sub-query of a sub-query.
- Index(es):
Relevant Pages
|
|
