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 ;
Art S. Kagel - 27 Aug 2007 20:00 GMT
> AIX 5.3 IDS 9.40.FC5
> How can this be re-written to eliminate the subquery?
[quoted text clipped - 21 lines]
> ,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
bozon - 28 Aug 2007 16:26 GMT
> > AIX 5.3 IDS 9.40.FC5
> > How can this be re-written to eliminate the subquery?
[quoted text clipped - 61 lines]
>
> 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.