Peoplesoft sub-query of a sub-query.

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 ;

On Aug 27, 1:19 pm, Roy Mercer <roy.mer...@gmail.com> wrote: > 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 ; I don't think you can fold a correllated sub-query used to obtain the result of an aggregation function for a filter in a where clause. So the 'select max(x4.effdt)...' has to stay. Folding the 'select x3.setid ...' is doable, however: 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, "sysadm".ps_set_cntrl_rec x3 WHERE x0.cust_status = 'A' AND x0.bill_to_flg = 'Y' AND x0.cust_level != 'P' AND x0.setid = x3.setid AND 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 ; You can also get rid of all those levels of nested parenthesis since it's all AND linkage logic. It's much easier to follow the logic that way. ;-) Art S. Kagel

On Aug 27, 3:00 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Aug 27, 1:19 pm, Roy Mercer <roy.mer...@gmail.com> wrote: > > > > > 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 ; > > I don't think you can fold a correllated sub-query used to obtain the > result of an aggregation function for a filter in a where clause. > So the 'select max(x4.effdt)...' has to stay. Folding the 'select > x3.setid ...' is doable, however: > > 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, > "sysadm".ps_set_cntrl_rec x3 > WHERE x0.cust_status = 'A' > AND x0.bill_to_flg = 'Y' > AND x0.cust_level != 'P' > AND x0.setid = x3.setid > AND 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 ; > > You can also get rid of all those levels of nested parenthesis since > it's all AND linkage logic. > It's much easier to follow the logic that way. ;-) > > Art S. Kagel Your formatting also makes the SQL much easier to read. Please post readable SQL. I would like to help but I don't want to spend my time formatting SQL to a readable format. In vesion 10 you could create a inline view that might make the SQL read a little easier but it would still have the same complexity. Creating a temp table of the max and then indexing it with update statistics may make the query faster because sometimes correlated subqueries aren't as fast as creating a temp table. It of course depends on several things.