SEO

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.