|
SELECT /*+opt_param('_optimizer_cost_based_transformation', 'off')*/
FN_GET_CRYPT ('D', D.RES_NO) AS EMP_REG_NO,
A.RENT_HABT_CLS AS A_RENT_HABT_CLS,
A.RENT_AGNT_NM AS A_RENT_AGNT_NM,
CASE WHEN A.RES_NO IS NOT NULL THEN FN_GET_CRYPT('D', A.RES_NO) END AS A_RES_NO,
NVL(A.RENT_CNTRCT_ADDR, ' ') AS A_RENT_CNTRCT_ADDR,
A.CNTRCT_FRM_DT AS A_CNTRCT_FRM_DT,
A.CNTRCT_TO_DT AS A_CNTRCT_TO_DT,
A.RENT_HOUSE_CLS AS A_RENT_HOUSE_CLS,
A.RENT_HOUSE_AREA AS A_RENT_HOUSE_AREA,
NVL(A.HOUSE_RENT, 0) AS A_HOUSE_RENT,
NVL(A.RENT_HABT_SUB_AMT, 0) AS A_RENT_HABT_SUB_AMT,
B.RENT_HABT_CLS AS B_RENT_HABT_CLS,
B.RENT_AGNT_NM AS B_RENT_AGNT_NM,
CASE WHEN B.RES_NO IS NOT NULL THEN FN_GET_CRYPT('D', B.RES_NO) END AS B_RES_NO,
B.CNTRCT_FRM_DT AS B_CNTRCT_FRM_DT,
B.CNTRCT_TO_DT AS B_CNTRCT_TO_DT,
NVL(B.BRRW_INTR_RAT, 0) AS B_BRRW_INTR_RAT,
NVL(B.PNINT_SUM, 0) AS B_PNINT_SUM,
NVL(B.PNINT_PRNCPAL, 0) AS B_PNINT_PRNCPAL,
NVL(B.PNINT_INTR, 0) AS B_PNINT_INTR,
NVL(B.RENT_HABT_SUB_AMT, 0) AS B_RENT_HABT_SUB_AMT,
C.RENT_HABT_CLS AS C_RENT_HABT_CLS,
C.RENT_AGNT_NM AS C_RENT_AGNT_NM,
CASE WHEN C.RES_NO IS NOT NULL THEN FN_GET_CRYPT('D', C.RES_NO) END AS C_RES_NO,
C.RENT_CNTRCT_ADDR AS C_RENT_CNTRCT_ADDR,
C.CNTRCT_FRM_DT AS C_CNTRCT_FRM_DT,
C.CNTRCT_TO_DT AS C_CNTRCT_TO_DT,
NVL(C.LFSTS_GRNTY_AMT, 0) AS C_LFSTS_GRNTY_AMT,
C.RENT_HOUSE_CLS AS C_RENT_HOUSE_CLS,
C.RENT_HOUSE_AREA AS C_RENT_HOUSE_AREA
FROM (SELECT ROWNUM AS CNT,
RENT_HABT_CLS,
RENT_AGNT_NM,
RES_NO,
RENT_CNTRCT_ADDR,
CNTRCT_FRM_DT,
CNTRCT_TO_DT,
HOUSE_RENT,
RENT_HABT_SUB_AMT,
RENT_HOUSE_CLS,
RENT_HOUSE_AREA
FROM PAY_WRK_RENT_HABT_SPEC
WHERE YY = #YY#
AND EMP_NO = #EMP_NO#
AND DUTY_DEG = #DUTY_DEG#
AND RENT_HABT_CLS = 'A0'
AND RENT_HABT_SUB_AMT > 0) A
FULL OUTER JOIN (SELECT ROWNUM AS CNT,
RENT_HABT_CLS,
RENT_AGNT_NM,
RES_NO,
CNTRCT_FRM_DT,
CNTRCT_TO_DT,
BRRW_INTR_RAT,
PNINT_SUM,
PNINT_PRNCPAL,
PNINT_INTR,
RENT_HABT_SUB_AMT
FROM PAY_WRK_RENT_HABT_SPEC
WHERE YY = #YY#
AND EMP_NO = #EMP_NO#
AND DUTY_DEG = #DUTY_DEG#
AND RENT_HABT_CLS = 'B0'
AND RENT_HABT_SUB_AMT > 0) B
ON A.CNT = B.CNT
FULL OUTER JOIN (SELECT ROWNUM AS CNT,
RENT_HABT_CLS,
RENT_AGNT_NM,
RES_NO,
RENT_CNTRCT_ADDR,
CNTRCT_FRM_DT,
CNTRCT_TO_DT,
LFSTS_GRNTY_AMT,
RENT_HOUSE_CLS,
RENT_HOUSE_AREA
FROM PAY_WRK_RENT_HABT_SPEC
WHERE YY = #YY#
AND EMP_NO = #EMP_NO#
AND DUTY_DEG = #DUTY_DEG#
AND RENT_HABT_CLS = 'C0'
AND EXISTS (SELECT *
FROM PAY_WRK_RENT_HABT_SPEC
WHERE YY = #YY#
AND EMP_NO = #EMP_NO#
AND DUTY_DEG = #DUTY_DEG#
AND RENT_HABT_CLS = 'B0' AND RENT_HABT_SUB_AMT > 0)) C
ON NVL(A.CNT, B.CNT) = C.CNT
LEFT OUTER JOIN PAY_WRK_FMLY D ON D.YY = #YY#
AND D.EMP_NO = #EMP_NO#
AND D.DUTY_DEG = #DUTY_DEG#
쿼리는 위와 같구요.
FULL OUTER JOIN 때문에 내부오류코드, 인수:[qcscpqbTxt],[600],[].... []
이런 오류가 나거든요.
오라클 버그로
VIEW MERGING, 서브쿼리 NESTING 이런 게 허용이 안되는거 같은데..
UNION ALL , GROUP BY 쓰면 된다고 본거같은데..
초보라 혼자 변경하기 조금 버거운 느낌입니다.
쿼리 잘짜시는분들 도와주십시요..
참고로 오라클 10G이며 패치를 한다던지 그런 건 할수없는 상황입니다.
|
