Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / Informix Topics / August 2007

Tip: Looking for answers? Try searching our database.

Peoplesoft sub-query of a sub-query.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Roy Mercer - 27 Aug 2007 18:19 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.