sureshbabu.jv
New User
Joined: 11 Apr 2006
Posts: 41
Location: Chennai
|
|
|
|
Hi,
I used the below query in my program, the job has been take much time to get completed that's why it's creating problem in production. I used UNION in the query to add rows, I heard that JOIN can take less time than UNION but I am not getting an idea to rewrite this query using JOIN clause instead of UNION.
Could you please give me suggestion to reqrite the below query using join clause. This is very important forme..
EXEC SQL DECLARE TIVAPSUM CURSOR FOR
SELECT
:BLANK-SUMMARY-IND,
APMST.REMIT_VEND_CODE,
APMST.REMIT_VEND_SUFFIX,
APMST.COMPANY,
APMST.EMPLOYEE_NUMBER,
APMST.BANK_ID,
APMST.BANK_ACCOUNT,
APMST.PAYMENT_STATUS,
APMST.INVOICE_STATUS,
APMST.PAYMENT_DATE,
APMST.PAYMT_REF_NO,
:PAYMENT-ALL-LINE-GLIT,
APMST.PAYMENT_AMT,
APMST.PAYMENT_AMT_FX,
APMST.INV_AMT_WITHHELD,
APMST.CURRENCY_CODE,
APMST.CURRENCY_CODE_FX,
APMST.CURRENCY_RATE_FROM,
APMST.PAYMENT_METHOD
FROM TIDAPMST APMST
WHERE
(APMST.INVOICE_TYPE NOT IN
(
:MODEL-7-LITRL,
:DEPOSIT-6-LITRL,
:WITHDRAW-8-LITRL))
AND ( ( (APMST.PAYMENT_STATUS IN
(
:APPROVED-28-LITRL,
:HOLD-29-LITRL,
:PAID-30-LITRL))
AND (APMST.INVOICE_STATUS IN
(
:ACCEPTED-2-LITRL,
:MISMATCH-4-LITRL,
:COMPLETE-3-LITRL)))
OR ( (APMST.INVOICE_STATUS = :MISMATCH-4-LITRL)
AND (APMST.PAYMENT_STATUS =
:APPROVED-28-LITRL))
OR ( (APMST.PAYMENT_STATUS IN
(
:PENDING-26-LITRL,
:REJECT-31-LITRL,
:REVIEW-32-LITRL))
AND (APMST.INVOICE_STATUS =
:ACCEPTED-2-LITRL)))
AND NOT EXISTS
(
SELECT
APLIN.PAYMT_REF_NO
FROM TIDAPLIN APLIN
WHERE
(APLIN.PAYMT_REF_NO =
APMST.PAYMT_REF_NO)
AND (APLIN.SUBLINE_LINE ?> :BLANK-SUBLINE-APCOM)
AND (((APLIN.LINE_PAY_STATUS ?=
APMST.PAYMENT_STATUS) AND
((APLIN.INVOICE_LINE ?=
:WITHHELD-LINE-APCOM ) OR
(APLIN.LINE_PAY_STATUS ?= :HOLD-10-LITRL )))
OR (APLIN.INVOICE_LINE_STAT ?=
APMST.INVOICE_STATUS))
)
UNION
SELECT
:BLANK-SUMMARY-IND,
APMST.REMIT_VEND_CODE,
APMST.REMIT_VEND_SUFFIX,
APMST.COMPANY,
APMST.EMPLOYEE_NUMBER,
APMST.BANK_ID,
APMST.BANK_ACCOUNT,
APLIN.LINE_PAY_STATUS,
APLIN.INVOICE_LINE_STAT,
APMST.PAYMENT_DATE,
APMST.PAYMT_REF_NO,
APLIN.INVOICE_LINE,
APLIN.PAYMENT_AMT,
APLIN.PAYMENT_AMT_FX,
:ZVAL-INV-AMT-WITHHELD,
APLIN.CURRENCY_CODE,
APLIN.CURRENCY_CODE_FX,
APLIN.CURRENCY_RATE_FROM,
APMST.PAYMENT_METHOD
FROM TIDAPMST APMST,
TIDAPLIN APLIN
WHERE
(APMST.PAYMT_REF_NO =
APLIN.PAYMT_REF_NO) AND
(APMST.INVOICE_TYPE NOT IN
(
:MODEL-7-LITRL,
:DEPOSIT-6-LITRL,
:WITHDRAW-8-LITRL)) AND
(APLIN.SUBLINE_LINE ?> :BLANK-SUBLINE-APCOM) AND
(((APLIN.LINE_PAY_STATUS IN
(
:APPROVED-9-LITRL,
:HOLD-10-LITRL,
:PAID-11-LITRL)) AND
(APLIN.INVOICE_LINE_STAT IN
(
:ACCEPTED-23-LITRL,
:MISMATCH-25-LITRL,
:COMPLETE-24-LITRL))) OR
((APLIN.INVOICE_LINE_STAT =
:MISMATCH-25-LITRL) AND
(APLIN.LINE_PAY_STATUS =
:APPROVED-9-LITRL)) OR
((APLIN.LINE_PAY_STATUS IN
(
:PENDING-27-LITRL,
:REJECT-12-LITRL,
:REVIEW-13-LITRL)) AND
(APLIN.INVOICE_LINE_STAT = :ACCEPTED-23-LITRL)))
AND EXISTS
(
SELECT
APLI1.PAYMT_REF_NO
FROM TIDAPLIN APLI1
WHERE
(APLI1.PAYMT_REF_NO =
APMST.PAYMT_REF_NO)
AND (APLI1.SUBLINE_LINE ?> :BLANK-SUBLINE-APCOM)
AND (((APLI1.LINE_PAY_STATUS ?=
APMST.PAYMENT_STATUS) AND
((APLI1.INVOICE_LINE ?=
:WITHHELD-LINE-APCOM ) OR
(APLI1.LINE_PAY_STATUS ?= :HOLD-10-LITRL )))
OR (APLI1.INVOICE_LINE_STAT ?=
APMST.INVOICE_STATUS))
)
END-EXEC.
Thanks,
Suresh |
|
