데이터베이스 질문과 답변
by 오델 [2015.10.14 16:08:51]
아래 쿼리를 돌리면...ORA-24344 에러가 나네요....
기존에 있던 쿼리인데...대체 어디가 잘못된건지 알 수 가 없네요...쿼리도 눈에 안들어 오고...T.T
고수님들의 가르침 부탁합니다.
SELECT JISA, SABUN, SA_NAME, PAY_INS, BAN_CNT, BAN_AMT, KIT_AMT, DONG_AMT,
SUP_AMT, STOT_AMT, GS_AMT, STOT_AMT - GS_AMT AS BGS_AMT, TRUNC (GS_AMT * 0.03, -1)
AS TAX1, TRUNC ((GS_AMT * 0.03) * 0.1, -1) AS TAX2, STOT_AMT - TRUNC (GS_AMT * 0.03, -1) -
TRUNC ((GS_AMT * 0.03) * 0.1, -1) AS S_CHA_AMT, POST_AMT, PATH_AMT, SIM_AMT,
BUJ_AMT, (POST_AMT + PATH_AMT + SIM_AMT + BUJ_AMT) AS BTOT_AMT, (STOT_AMT - (TRUNC (GS_AMT * 0.03, -1)) -
TRUNC ((GS_AMT * 0.03) * 0.1, -1)) + (POST_AMT + PATH_AMT + SIM_AMT + BUJ_AMT)
AS TOTAMT
FROM (
SELECT FN_CODE_NAME ('CODEDT', '06', A.JISA) AS JISA, A.SABUN, A.SA_NAME,
A.PAY_INS, NVL(SUM(C.BAN_CNT), 0) AS BAN_CNT, NVL(SUM(B.BAN_AMT), 0) AS BAN_AMT,
NVL(A.PAY_ETC, 0) AS KIT_AMT, NVL(SUM(B.BAN_AMT), 0) + NVL(PAY_ETC, 0) + NVL(H.DONG_AMT, 0) +
NVL(SUM(SP_SIGN), 0) * TO_NUMBER(CASE WHEN A.PAY_INS IN ('E', 'F') THEN (FN_CODE_NAME ('CODEBIGO', 'S92', '04'))
ELSE (FN_CODE_NAME ('CODEBIGO', 'S92', '03')) END) AS STOT_AMT, ((NVL(SUM(B.BAN_AMT), 0) +
NVL(PAY_ETC, 0) + NVL(SUM(SP_SIGN), 0) * TO_NUMBER(CASE WHEN A.PAY_INS IN ('E', 'F')
THEN (FN_CODE_NAME ('CODEBIGO', 'S92', '04')) ELSE (FN_CODE_NAME ('CODEBIGO', 'S92', '03'))
END)) * 0.65) GS_AMT, NVL(SUM(CASE WHEN F.ACCOUNT = '02' THEN F.AMT ELSE 0 END), 0)
AS POST_AMT, NVL(SUM(CASE WHEN F.ACCOUNT = '01' THEN F.AMT ELSE 0 END), 0) AS
PATH_AMT, NVL(SUM(SIM_CNT), 0) * TO_NUMBER(FN_CODE_NAME ('CODEBIGO', 'S92', '01'))
AS SIM_AMT, NVL(SUM(OUT_CNT), 0) * TO_NUMBER(FN_CODE_NAME ('CODEBIGO', 'S92', '02'))
BUJ_AMT, NVL(H.DONG_AMT, 0) AS DONG_AMT, NVL(SUM(SP_SIGN), 0) * TO_NUMBER(CASE
WHEN A.PAY_INS IN ('E', 'F') THEN (FN_CODE_NAME ('CODEBIGO', 'S92', '04')) ELSE (FN_CODE_NAME ('CODEBIGO', 'S92', '03'))
END) AS SUP_AMT
FROM S01_SABUN A LEFT OUTER JOIN (
SELECT INSUR_CD, YYYYMMDD, ORDER_NO, SABUN, FN_NUR_PAY (SABUN, YYYYMMDD) AS
BAN_AMT, (CASE WHEN SP_SIGN = 'Y' THEN 1 ELSE 0 END) AS SP_SIGN
FROM S02_ORDER_NURCM) B ON A.SABUN = B.SABUN JOIN (
SELECT INSUR_CD, YYYYMMDD, ORDER_NO, 1 AS BAN_CNT, COMP_DT
FROM S02_ORDERCM
WHERE TO_NUMBER(TO_CHAR(COMP_DT, 'YYYYMMDD')) BETWEEN '20150901'
AND '20150930') C ON B.INSUR_CD = C.INSUR_CD
AND B.YYYYMMDD = C.YYYYMMDD AND B.ORDER_NO = C.ORDER_NO LEFT OUTER JOIN (
SELECT INSUR_CD, YYYYMMDD, ORDER_NO, COUNT(*) AS OUT_CNT
FROM S02_OUT
WHERE YSIGN = '1'
GROUP BY INSUR_CD, YYYYMMDD, ORDER_NO) D ON C.INSUR_CD = D.INSUR_CD
AND C.YYYYMMDD = D.YYYYMMDD AND C.ORDER_NO = D.ORDER_NO LEFT OUTER JOIN
S03_NUR_COST F ON F.YYYYMMDD BETWEEN '20150901'
AND '20150930' AND A.SABUN = F.SABUN LEFT OUTER JOIN (
SELECT INSUR_CD, YYYYMMDD, ORDER_NO, 1 AS SIM_CNT
FROM S02_JINDANDT
WHERE JINDAN_CD = 'ITM0001451' AND DELSIGN = 'N'
GROUP BY INSUR_CD, YYYYMMDD, ORDER_NO) G ON C.INSUR_CD = G.INSUR_CD
AND C.YYYYMMDD = G.YYYYMMDD AND C.ORDER_NO = G.ORDER_NO LEFT OUTER JOIN (
SELECT SABUN, SUM(AMT) AS DONG_AMT
FROM S03_NUR_DONGHANG
WHERE YYYYMMDD BETWEEN '20150901' AND '20150930'
GROUP BY SABUN) H ON A.SABUN = H.SABUN
WHERE A.JIKCHAK = '08'
GROUP BY A.JISA, A.SABUN, A.SA_NAME, A.PAY_INS, A.PAY_ETC, H.DONG_AMT) A
ORDER BY JISA, SA_NAME
;
NULL 이 있는 데이터를 SUM, COUNT등을 했을때 에러가 발생하기도 합니다.
기존에는 NULL인 데이터가 없었는데.. 이후에 생겨서 나는 에러로 추정 됩니다..
데이터를 확인 해 보셔야 할 듯 합니다.
일단 쿼리를 가독성 있게 변경 ?.... 고수님들이 좀 확인하기 쉽도록.....
SELECT JISA,
SABUN,
SA_NAME,
PAY_INS,
BAN_CNT,
BAN_AMT,
KIT_AMT,
DONG_AMT,
SUP_AMT,
STOT_AMT,
GS_AMT,
STOT_AMT - GS_AMT AS BGS_AMT,
TRUNC (GS_AMT * 0.03, -1) AS TAX1,
TRUNC (( GS_AMT * 0.03 ) * 0.1, -1) AS TAX2,
STOT_AMT - TRUNC (GS_AMT * 0.03, -1) - TRUNC (( GS_AMT * 0.03 ) * 0.1, -1) AS S_CHA_AMT,
POST_AMT,
PATH_AMT,
SIM_AMT,
BUJ_AMT,
( POST_AMT + PATH_AMT + SIM_AMT + BUJ_AMT ) AS BTOT_AMT,
( STOT_AMT - ( TRUNC (GS_AMT * 0.03, -1) ) - TRUNC (( GS_AMT * 0.03 ) * 0.1, -1) ) + ( POST_AMT + PATH_AMT + SIM_AMT + BUJ_AMT ) AS TOTAMT
FROM (SELECT FN_CODE_NAME ('CODEDT', '06', A.JISA) AS JISA,
A.SABUN,
A.SA_NAME,
A.PAY_INS,
NVL(SUM(C.BAN_CNT), 0) AS BAN_CNT,
NVL(SUM(B.BAN_AMT), 0) AS BAN_AMT,
NVL(A.PAY_ETC, 0) AS KIT_AMT,
NVL(SUM(B.BAN_AMT), 0) + NVL(PAY_ETC, 0)
+ NVL(H.DONG_AMT, 0) + NVL(SUM(SP_SIGN), 0) * TO_NUMBER(CASE
WHEN A.PAY_INS IN ( 'E', 'F' ) THEN ( FN_CODE_NAME ('CODEBIGO', 'S92', '04') )
ELSE ( FN_CODE_NAME ('CODEBIGO', 'S92', '03') )
END) AS STOT_AMT,
( ( NVL(SUM(B.BAN_AMT), 0) + NVL(PAY_ETC, 0) + NVL(SUM(SP_SIGN), 0) * TO_NUMBER(CASE
WHEN A.PAY_INS IN ( 'E', 'F' ) THEN ( FN_CODE_NAME ('CODEBIGO', 'S92', '04') )
ELSE ( FN_CODE_NAME ('CODEBIGO', 'S92', '03') )
END) ) * 0.65 ) GS_AMT,
NVL(SUM(CASE
WHEN F.ACCOUNT = '02' THEN F.AMT
ELSE 0
END), 0) AS POST_AMT,
NVL(SUM(CASE
WHEN F.ACCOUNT = '01' THEN F.AMT
ELSE 0
END), 0) AS PATH_AMT,
NVL(SUM(SIM_CNT), 0) * TO_NUMBER(FN_CODE_NAME ('CODEBIGO', 'S92', '01')) AS SIM_AMT,
NVL(SUM(OUT_CNT), 0) * TO_NUMBER(FN_CODE_NAME ('CODEBIGO', 'S92', '02')) BUJ_AMT,
NVL(H.DONG_AMT, 0) AS DONG_AMT,
NVL(SUM(SP_SIGN), 0) * TO_NUMBER(CASE
WHEN A.PAY_INS IN ( 'E', 'F' ) THEN ( FN_CODE_NAME ('CODEBIGO', 'S92', '04') )
ELSE ( FN_CODE_NAME ('CODEBIGO', 'S92', '03') )
END) AS SUP_AMT
FROM S01_SABUN A
LEFT OUTER JOIN (SELECT INSUR_CD,
YYYYMMDD,
ORDER_NO,
SABUN,
FN_NUR_PAY (SABUN, YYYYMMDD) AS BAN_AMT,
( CASE
WHEN SP_SIGN = 'Y' THEN 1
ELSE 0
END ) AS SP_SIGN
FROM S02_ORDER_NURCM) B
ON A.SABUN = B.SABUN
JOIN (SELECT INSUR_CD,
YYYYMMDD,
ORDER_NO,
1 AS BAN_CNT,
COMP_DT
FROM S02_ORDERCM
WHERE TO_NUMBER(TO_CHAR(COMP_DT, 'YYYYMMDD')) BETWEEN '20150901' AND '20150930') C
ON B.INSUR_CD = C.INSUR_CD
AND B.YYYYMMDD = C.YYYYMMDD
AND B.ORDER_NO = C.ORDER_NO
LEFT OUTER JOIN (SELECT INSUR_CD,
YYYYMMDD,
ORDER_NO,
COUNT(*) AS OUT_CNT
FROM S02_OUT
WHERE YSIGN = '1'
GROUP BY INSUR_CD,
YYYYMMDD,
ORDER_NO) D
ON C.INSUR_CD = D.INSUR_CD
AND C.YYYYMMDD = D.YYYYMMDD
AND C.ORDER_NO = D.ORDER_NO
LEFT OUTER JOIN S03_NUR_COST F
ON F.YYYYMMDD BETWEEN '20150901' AND '20150930'
AND A.SABUN = F.SABUN
LEFT OUTER JOIN (SELECT INSUR_CD,
YYYYMMDD,
ORDER_NO,
1 AS SIM_CNT
FROM S02_JINDANDT
WHERE JINDAN_CD = 'ITM0001451'
AND DELSIGN = 'N'
GROUP BY INSUR_CD,
YYYYMMDD,
ORDER_NO) G
ON C.INSUR_CD = G.INSUR_CD
AND C.YYYYMMDD = G.YYYYMMDD
AND C.ORDER_NO = G.ORDER_NO
LEFT OUTER JOIN (SELECT SABUN,
SUM(AMT) AS DONG_AMT
FROM S03_NUR_DONGHANG
WHERE YYYYMMDD BETWEEN '20150901' AND '20150930'
GROUP BY SABUN) H
ON A.SABUN = H.SABUN
WHERE A.JIKCHAK = '08'
GROUP BY A.JISA,
A.SABUN,
A.SA_NAME,
A.PAY_INS,
A.PAY_ETC,
H.DONG_AMT) A
ORDER BY JISA,
SA_NAME
) 버튼을 클릭하여 작성 하시면 됩니다.