Re: [sqr-users] Oracle Insert/Select From issue

[Aleksandr Slobodyanik <aslobody@yahoo.com>]

Just want to add my 5 cents...
Move subquery to the bottom of the where clause.

"Hiney, Edward (CLAIM, Claims)" <Edward.Hiney@thehartford.com> wrote:
All,
Here's the problem.
We have an SQR with code (below) found in a procedure that runs for a VERY LONG 
TIME (HOURS).I tried separating in into a "Select" and an "Insert" but that did 
not help. If anyone can come up with a solution to break up the WHERE clause 
into 2 parts (doing the 2nd join to PS_VCHR_ACCTG_LINE D ) I think that would 
do it. It's not as easy as it looks.

We did an Oracle SQL trace, so we know that this is where the bottleneck is. We 
also pu SHOW stmts in the SQR.

The caveat is that when this sql is executed in SQL-plus or TOAD, it runs in 2 
minutes! Any ideas would be appreciated.


TIA.
ed.hiney




Begin-SQL on-error=170-sql-error
INSERT INTO PS_HIG_RPT7X_DTL SELECT 
0,
'71F',
$Tdate,
B.BANK_SETID, 
B.BANK_CD , 
B.BANK_ACCT_KEY,
A.ACCOUNTING_DT, 
1,
3,
C.HIG_CCPS_IND,
C.HIG_SRS_IND, 
&HIG_FREQ_IND,
SUM(A.MONETARY_AMOUNT - ABS(D.MONETARY_AMOUNT)) 
FROM PS_VCHR_ACCTG_LINE A, PS_PYMNT_VCHR_XREF B , PS_HIG_BPROFL_VW C, 
PS_VCHR_ACCTG_LINE D
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT 
AND A.VOUCHER_ID = B.VOUCHER_ID 
AND A.PYMNT_CNT = B.PYMNT_CNT
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT 
AND C.ORIGIN = 'ONL' 
AND a.monetary_amount > 0
and b.pymnt_gross_amt > 0
AND a.accounting_dt between $Fdate and $Tdate
And a.appl_jrnl_id = 'PAYMENTS'
And a.posting_process = 'PYMN'
And a.dst_acct_type = 'APA'
And a.pymnt_cnt = (select min(z.pymnt_cnt)
from ps_vchr_acctg_line z
where z.business_unit = a.business_unit
and z.voucher_id = a.voucher_id
and z.appl_jrnl_id = 'PAYMENTS'
and z.posting_process = 'PYMN'
and z.dst_acct_type = 'APA')
And d.business_unit = a.business_unit
And d.voucher_id = a.voucher_id
And d.accounting_dt < a.accounting_dt
And d.appl_jrnl_id = 'ACCRUAL'
And d.posting_process = 'ACCR'
And d.dst_acct_type = 'APA'
And d.unpost_seq = a.unpost_seq
And abs(a.monetary_amount) <> abs(d.monetary_amount)
GROUP BY B.BANK_SETID, B.BANK_CD , B.BANK_ACCT_KEY, 
a.accounting_dt,C.HIG_CCPS_IND,C.HIG_SRS_IND
end-sql



Thank you,

Ed Hiney
PeopleSoft Developer
Claims IT
The Hartford
edward.hiney@thehartford.com
860-547-3227





*************************************************************************
This communication, including attachments, is
for the exclusive use of addressee and may contain proprietary,
confidential and/or privileged information. If you are not the intended
recipient, any use, copying, disclosure, dissemination or distribution is
strictly prohibited. If you are not the intended recipient, please notify
the sender immediately by return e-mail, delete this communication and
destroy all copies.
*************************************************************************

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

                
---------------------------------
Yahoo! for Good
 Click here to donate to the Hurricane Katrina relief effort. 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users