[sqr-users] Urgent help needed !!

[Manuel_Garcia@hdm.honda.com]

Hi all,


I'm writing a sqr program and I'm having a performance problem with this
statement:


BEGIN-SQL
INSERT into ps_hdm_saldos_tmp using
SELECT
D.VOUCHER_ID,
E.PYMNT_CNT,
D.INVOICE_ID,
D.VENDOR_ID,
D.INVOICE_DT,
D.ACCOUNTING_DT,
D.FOREIGN_AMOUNT,
D.FOREIGN_CURRENCY,
E.RATE_MULT,
D.MONETARY_AMOUNT,
E.PYMNT_GROSS_AMT,
E.SCHEDULED_PAY_DT,
E.PYMNT_SELCT_STATUS,
E.DSCNT_DUE_DT
FROM PS_HDM_VOUCHER_TMP D, PS_PYMNT_VCHR_XREF E
WHERE D.VOUCHER_ID = E.VOUCHER_ID
AND E.PYMNT_SELCT_STATUS <> 'P'
AND E.PYMNT_SELCT_STATUS <> 'X'
AND D.ACCOUNTING_DT <= $fecha
AND ( D.CLOSE_STATUS <> 'C'
OR ( D.CLOSE_STATUS = 'C'
AND D.MANUAL_CLOSE_DT > $fecha))
;
COMMIT;
END-SQL

My key fields are:

PS_HDM_VOUCHER_TMP:
voucher_id

PS_PYMNT_VCHR_XREF:
business_unit,
voucher_id,
pymnt_count.


If I run a query oustide sqr (PS Query for instance), it runs almost
immediately,
SELECT
D.VOUCHER_ID,
E.PYMNT_CNT,
D.INVOICE_ID,
D.VENDOR_ID,
D.INVOICE_DT,
D.ACCOUNTING_DT,
D.FOREIGN_AMOUNT,
D.FOREIGN_CURRENCY,
E.RATE_MULT,
D.MONETARY_AMOUNT,
E.PYMNT_GROSS_AMT,
E.SCHEDULED_PAY_DT,
E.PYMNT_SELCT_STATUS,
E.DSCNT_DUE_DT
FROM PS_HDM_VOUCHER_TMP D, PS_PYMNT_VCHR_XREF E
WHERE D.VOUCHER_ID = E.VOUCHER_ID
AND E.PYMNT_SELCT_STATUS <> 'P'
AND E.PYMNT_SELCT_STATUS <> 'X'
AND D.ACCOUNTING_DT <= $fecha
AND ( D.CLOSE_STATUS <> 'C'
OR ( D.CLOSE_STATUS = 'C'
AND D.MANUAL_CLOSE_DT > $fecha))
;


I have similar statements along my program and there's no problem whit
them, the next, for instance, it runs quick:

BEGIN-SQL

INSERT into ps_hdm_saldos_tmp using
SELECT
A.VOUCHER_ID,
B.PYMNT_CNT,
A.INVOICE_ID,
A.VENDOR_ID,
A.INVOICE_DT,
A.ACCOUNTING_DT,
A.FOREIGN_AMOUNT,
A.FOREIGN_CURRENCY,
B.RATE_MULT,
A.MONETARY_AMOUNT,
B.PYMNT_GROSS_AMT,
B.SCHEDULED_PAY_DT,
B.PYMNT_SELCT_STATUS,
C.PYMNT_DT
FROM PS_HDM_VOUCHER_TMP A, PS_PYMNT_VCHR_XREF B, PS_PAYMENT_TBL C
WHERE A.VOUCHER_ID = B.VOUCHER_ID
AND C.BANK_SETID = B.BANK_SETID
AND C.BANK_CD = B.BANK_CD
AND C.BANK_ACCT_KEY = B.BANK_ACCT_KEY
AND C.PYMNT_ID = B.PYMNT_ID
AND A.ACCOUNTING_DT <= $fecha
AND B.PYMNT_SELCT_STATUS = 'P'
AND C.ACCOUNTING_DT > $fecha
;
COMMIT;
END-SQL




Thanks.







Juan Manuel García
Tecnología de Información
Honda de Mexico S.A. de C.V.
Tel: (01 33) 328 40135























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