Is there anyway to fine tune the below SQL query. It is taking longer time to execute,
Code: Select all
EXEC SQL
UPDATE ENC_CMSTAT_SUM S
SET ENC_COUNT = (SELECT COUNT(*)
FROM (
SELECT C.MF_ID, C.IN_INTRCHG_SNDR_ID,
C.WTX_CLAIM_REF_NBR,
C.WTX_CLAIM_REV_NBR,
C.PROCESS_STATUS
FROM ENC_837I_CLM C
WHERE C.MF_ID = S.MF_ID
AND C.PROCESS_STATUS =
S.PROCESS_STATUS
AND C.OVERRIDE_IND = 'N'
AND C.IN_INTRCHG_SNDR_ID =
S.INTRCHG_SNDR_ID
GROUP BY c.MF_ID,
C.IN_INTRCHG_SNDR_ID,
C.WTX_CLAIM_REF_NBR,
C.WTX_CLAIM_REV_NBR,
C.PROCESS_STATUS
HAVING MAX(LEFT(C.TRANS_FILE_DT,6))
= S.DATE_YRMO
) TEMP
)
WHERE S.ENC_TYPE = 'I'
AND S.DATE_IND = '0'
AND S.MF_ID = :WS-MF-ID