Re: Peoplesoft sub-query of a sub-query.

On Aug 27, 3:00 pm, "Art S. Kagel" <art.ka...@xxxxxxxxx> wrote:
On Aug 27, 1:19 pm, Roy Mercer <roy.mer...@xxxxxxxxx> 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.

.

Relevant Pages

  • Re: mySQL Problem
    ... sql servers) specify precisely where the problem is - this time it ... are things called RESERVED WORDS. ... by virtually any programmer I know. ... *formatting* said query so you could break it down and isolate the problem. ...
    (comp.lang.php)
  • Re: Set based solution
    ... And SQL IS a data manipulation language, hence my problem with not using it ... that I had always used SQL Server to do data formatting for the past 12 ... instead of coding hooks in a dll to do this formatting, ... SQL, and T-SQL, is not a general purpose programming language. ...
    (microsoft.public.sqlserver.programming)
  • Re: mySQL Problem
    ... sql servers) specify precisely where the problem is - this time it ... are things called RESERVED WORDS. ... Steve - I don't agree with your method of "properly formatting" the ... virtually any programmer I know. ...
    (comp.lang.php)
  • Re: Set based solution
    ... coding off the database server, then it is worthwhile doing it. ... I'd argue that formatting things that differ from country ... > And SQL IS a data manipulation language, hence my problem with not using ...
    (microsoft.public.sqlserver.programming)
  • Re: mySQL Problem
    ... sql servers) specify precisely where the problem is - this time it ... queries code readability you are, of course, right, since a programmer ... are things called RESERVED WORDS. ... about formatting. ...
    (comp.lang.php)