같이사자 :: 포인트 결제 쿼리문 오류 수정

 

기존 포인트 합계 VIEW 쿼리문은 다음과 같았다.

-- 총 합계 VIEW
CREATE OR REPLACE VIEW VIEW_POINT
AS
SELECT M.CODE, (NVL(R.AMOUNT, 0) + NVL(C.AMOUNT, 0) - NVL(P.AMOUNT, 0) - NVL(W.AMOUNT, 0) + NVL(CH.AMOUNT, 0)) AS POINT
FROM MEMBER M FULL OUTER JOIN VIEW_REFUND R 
                ON M.CODE = R.MEMBER_CODE
                FULL OUTER JOIN VIEW_COMPLETE C
                ON R.MEMBER_CODE = C.MEMBER_CODE
                FULL OUTER JOIN VIEW_PAYMENT P
                ON C.MEMBER_CODE = P.MEMBER_CODE
                FULL OUTER JOIN VIEW_WITHDRAWAL W
                ON P.MEMBER_CODE = W.MEMBER_CODE
                FULL OUTER JOIN VIEW_CHARGE CH
                ON W.MEMBER_CODE = CH.MEMBER_CODE
WHERE M.CODE IS NOT NULL;

 

그런데 엄청난 오류가 있었다.

 

두 번째 JOIN 하는 과정부터 JOIN 하는 조건이 잘못됐다.

 

FULL OUTER JOIN VIEW_COMPLETE C
ON R.MEMBER_CODE = C.MEMBER_CODE

 

 

이렇게 R.MEMBER_CODE 를 조건으로 넣어주게 되면 REFUND (환불) 테이블에 있는

 

회원코드로만 연결이 된다..

 

따라서 아래와 같이 변경해주었다.

 

 

CREATE OR REPLACE VIEW VIEW_POINT
AS
SELECT M.CODE, (NVL(R.AMOUNT, 0) + NVL(C.AMOUNT, 0) - NVL(P.AMOUNT, 0) - NVL(W.AMOUNT, 0) + NVL(CH.AMOUNT, 0)) AS POINT
FROM MEMBER M FULL OUTER JOIN VIEW_REFUND R 
                ON M.CODE = R.MEMBER_CODE
                FULL OUTER JOIN VIEW_COMPLETE C
                ON M.CODE = C.MEMBER_CODE
                FULL OUTER JOIN VIEW_PAYMENT P
                ON M.CODE = P.MEMBER_CODE
                FULL OUTER JOIN VIEW_WITHDRAWAL W
                ON M.CODE = W.MEMBER_CODE
                FULL OUTER JOIN VIEW_CHARGE CH
                ON M.CODE = CH.MEMBER_CODE
WHERE M.CODE IS NOT NULL;

 

 

이렇게 VIEW 쿼리문만 변경해주니 오류가 사라졌당!

 

다음부턴 조심하자.