안녕하세요..
요구조건 변경으로 쿼리문을 수정했는데..
쿼리만 따로 떼서 실행하면 1분정도 걸리는 쿼리가 프로시저에 넣고 돌리면 응답이 없습니다..
변경 전 쿼리는 잘 돌구요..
도대체 뭐가 원인인지 모르겠습니다.. 도와주세요 ㅠㅠ
/***************************************************************************************************************
INSERT INTO 대상테이블
SELECT A.*
, B.CNO
FROM CR.TCNE_TBDA_DLY_INFO@RSSANTA A
, TRSW_ERARATGPROD B
WHERE A.CONO = B.CONO
-- AND (A.DLY_ORG_C, A.ACNO, A.BAS_DATE) IN (
-- SELECT DLY_ORG_C, ACNO, MAX(BAS_DATE)
AND (A.CONO, NVL(A.BSNO, 'Z'), A.DLY_ORG_C, A.ACNO, NVL(A.MNG_BR_C, 'Z'), NVL(A.ITEM_C, 'Z'), NVL(A.REG_RSN_C, 'Z'), NVL(A.LN_BAL, 0), NVL(A.DLY_AMT, 0), NVL(A.DLY_RCKN_DATE, '99991231'), NVL(A.DLY_RELS_DATE, '99991231'), A.BAS_DATE)
IN (
SELECT CONO, NVL(BSNO, 'Z'), DLY_ORG_C, ACNO, NVL(MNG_BR_C, 'Z'), NVL(ITEM_C, 'Z'), NVL(REG_RSN_C, 'Z'), NVL(LN_BAL, 0), NVL(DLY_AMT, 0), NVL(DLY_RCKN_DATE, '99991231'), NVL(DLY_RELS_DATE, '99991231'), MAX(BAS_DATE)
FROM CR.TCNE_TBDA_DLY_INFO@RSSANTA
WHERE BAS_DATE <= V_BAS_DATE
AND ((DLY_AMT >= 100000 AND TR_FORM_C IN ('01', '02', '03', '99'))
OR TR_FORM_C = '04')
AND (V_BAS_DATE BETWEEN DLY_RCKN_DATE AND NVL(DLY_RELS_DATE, '99991231')
OR TO_CHAR(ADD_MONTHS(V_BAS_DATE, -6), 'YYYYMMDD') BETWEEN DLY_RCKN_DATE AND NVL(DLY_RELS_DATE, '99991231'))
-- GROUP BY DLY_ORG_C, ACNO
GROUP BY CONO, NVL(BSNO, 'Z'), DLY_ORG_C, ACNO, NVL(MNG_BR_C, 'Z'), NVL(ITEM_C, 'Z'), NVL(REG_RSN_C, 'Z'), NVL(LN_BAL, 0), NVL(DLY_AMT, 0), NVL(DLY_RCKN_DATE, '99991231'), NVL(DLY_RELS_DATE, '99991231')
)
AND B.BAS_DATE = V_BAS_DATE
***************************************************************************************************************/
주석으로 막힌 부분은 변경 전이고, 붉은색 부분이 변경 후 입니다..
|