쿼리 12번

쿼리 13번 문제
개발/유지보수 요청 정보를 출력하세요
검토내용, 테스트 데이터가 있을 경우 검토내용도 함께 출력하세요
코드값은 코드명칭으로 변경하여 출력하고 사번은 사원명으로 변경하여 출력하세요
대상테이블
A11.A1CT_ASK : 정보시스템 개발/유지보수 요청
A11.A1CT_REVIEW : 정보시스템 개발/유지보수 검토
A11.A1CT_MAINTER_DETAIL_WORKS : 유지보수 작업정보
출력정보
의뢰번호, 업무구분(A10030), 세부구분(A10036), 의뢰구분(A10035), 의뢰유형(A10033), 진행구분(A10037), 의뢰일자, 의뢰자
, 검토자, 검토구분(A10038), 검토난이도(A10034), 개발자접수일자, 개발담당자(A10039)
, 작업번호, 작업 상태(A10042), 작업내용, 테스트 요청일, 배포요청일, 배포완료일, 배포확인일시, 배포확인자

SELECT A.MAINTER_NUM AS "의뢰번호"
    , SYSTEM_FLAG AS "업무구분" 
    , SUBSYSTEM_FLAG AS "세부구분" 
    , ASK_FLAG AS "의뢰구분" 
    , ASK_TYPE AS "의뢰유형" 
    , PROCESS_FLAG AS "진행구분" 
    , ASK_YMD AS "의뢰일자"
    , ASK_EMPID AS "의뢰자"
    , REVIEW_EMPID AS "검토자"
    , REVIEW_FLAG AS "검토구분" 
    , REVIEW_LEVEL AS "검토난이도" 
    , DEV_RECEIVE_YMD AS "개발자접수일자"
    , DEV_NAME_CODE AS "개발담당자" 
    , WORK_NUM AS "작업번호"
    , WORK_STS AS "작업상태"
--    , WORK_DESC AS "작업내용"
    , TEST_REQ_YMD AS "테스트 요청일"   
    , DEPLOY_REQ_YMD AS "배포요청일"
    , DEPLOY_YMD AS "배포완료일"
    , DEPLOY_CONF_EMPID AS "배포확인자"
FROM A11.A1CT_ASK A
LEFT JOIN A11.A1CT_REVIEW R
ON A.MAINTER_NUM = R.MAINTER_NUM 
LEFT JOIN A11.A1CT_MAINTER_DETAIL_WORKS W
ON W.MAINTER_NUM = A.MAINTER_NUM
WHERE A.MAINTER_NUM LIKE '202201%'
-- AND W.MAINTER_NUM IS NULL
ORDER BY A.MAINTER_NUM, WORK_NUM;

위 쿼리를 돌린 결과다. 값들이 모두 코드화 되어 있다. 정확한 내용을 알기 위해서는 그룹웨어에서 각 컬럼의 CODE_ID를 확인하고 코드 테이블에서 각 CODE_ID의 값들이 의미하는 매칭해야 한다.

WITH TEST AS (
            SELECT A.MAINTER_NUM, SYSTEM_FLAG,SUBSYSTEM_FLAG, ASK_FLAG
            FROM A11.A1CT_ASK A, A11.A1CT_REVIEW R
            WHERE A.MAINTER_NUM = R.MAINTER_NUM
            AND A.MAINTER_NUM LIKE '202209%'
)
SELECT A.MAINTER_NUM, A.ITEM_KNAME AS "업무구분", B.ITEM_KNAME AS "세부구분", C.ITEM_KNAME AS "의뢰구분" 
FROM
(
    SELECT MAINTER_NUM, ITEM_KNAME
    FROM A11.A1AT_CODE_B B,
    TEST C
    WHERE B.CODE_ITEM = C.SYSTEM_FLAG AND B.CODE_ID = 'A10030'
) A
,
(
    SELECT MAINTER_NUM, ITEM_KNAME
    FROM A11.A1AT_CODE_B B,
    TEST C
    WHERE B.CODE_ITEM = C.SUBSYSTEM_FLAG AND B.CODE_ID = 'A10036'
) B
,
(
    SELECT MAINTER_NUM, ITEM_KNAME
    FROM A11.A1AT_CODE_B B,
    TEST C
    WHERE B.CODE_ITEM = C.ASK_FLAG AND B.CODE_ID = 'A10035'
) C
WHERE A.MAINTER_NUM = B.MAINTER_NUM AND B.MAINTER_NUM= C.MAINTER_NUM;

...(생략) 

처음에는 무지성으로 SELECT ~ FROM 절을 계속 조인하는 방식으로 코드를 짰다. 내가 짜면서도 이건 너무 비효율적인데(?)...라는 생각이 들었고 사수님께서도 '완전 별로다...'라는 답변을 받았다. 이렇게 쿼리를 짜면 WHERE 절이 너무 길어지고, 성능도 매우 떨어진다.

SELECT A.MAINTER_NUM AS "의뢰번호"
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10030' AND CODE_ITEM=SYSTEM_FLAG) AS "업무구분" 
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10030',SYSTEM_FLAG) AS "업무구분" 
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10036' AND CODE_ITEM=SUBSYSTEM_FLAG) AS "세부구분"
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10035' AND CODE_ITEM=ASK_FLAG) AS "의뢰구분"
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10033' AND CODE_ITEM=ASK_TYPE) AS "의뢰유형"
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10037' AND CODE_ITEM=PROCESS_FLAG) AS "진행구분"
    , ASK_YMD AS "의뢰일자"
    , (SELECT NM_KOR FROM A11.A1AT_USER_MASTER WHERE EMPLOYEE_ID = ASK_EMPID) AS "의뢰자"
    , (SELECT NM_KOR FROM A11.A1AT_USER_MASTER WHERE EMPLOYEE_ID = REVIEW_EMPID) AS "검토자"
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10038' AND CODE_ITEM=REVIEW_FLAG) AS "검토구분"
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10034' AND CODE_ITEM=REVIEW_LEVEL) AS "검토난이도"
    , DEV_RECEIVE_YMD AS "개발자접수일자"
    , (SELECT ITEM_KNAME FROM A11.A1AT_CODE_B  WHERE CODE_ID = 'A10039' AND CODE_ITEM=DEV_NAME_CODE) AS "개발담당자"
FROM A11.A1CT_ASK A
LEFT JOIN A11.A1CT_REVIEW R
ON A.MAINTER_NUM = R.MAINTER_NUM 
WHERE A.MAINTER_NUM LIKE '202209%';

from 절로 테이블을 계속 조인하는 것이 아니라, SELECT 절에서 해결하는 방식으로 수정하니 쿼리가 훨씬 깔끔해지고 가독성이 높아졌다. 코드 ID 값으로 실제 값을 매칭하는 일은 매우 잦을텐데 매번 이렇게 작성하는 것이 맞을까?

SELECT A.MAINTER_NUM AS "의뢰번호"
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10030',SYSTEM_FLAG) AS "업무구분" 
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10036',SUBSYSTEM_FLAG) AS "업무구분" 
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10035',ASK_FLAG) AS "업무구분" 
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10033',ASK_TYPE) AS "업무구분" 
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10037',PROCESS_FLAG) AS "업무구분" 
    , ASK_YMD AS "의뢰일자"
    , (SELECT NM_KOR FROM A11.A1AT_USER_MASTER WHERE EMPLOYEE_ID = ASK_EMPID) AS "의뢰자"
    , (SELECT NM_KOR FROM A11.A1AT_USER_MASTER WHERE EMPLOYEE_ID = REVIEW_EMPID) AS "검토자"
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10038',REVIEW_FLAG) AS "검토구분"
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10034',REVIEW_LEVEL) AS "검토난이도"
    , DEV_RECEIVE_YMD AS "개발자접수일자"
    , A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10039',DEV_NAME_CODE) AS "개발담당자"
FROM A11.A1CT_ASK A
LEFT JOIN A11.A1CT_REVIEW R
ON A.MAINTER_NUM = R.MAINTER_NUM 
WHERE A.MAINTER_NUM LIKE '202209%';

역시 같은 기능을 해주는 함수가 이미 만들어져 있었다.

SELECT A.MAINTER_NUM AS "의뢰번호"
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10030',SYSTEM_FLAG) from dual) AS "업무구분" 
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10036',SUBSYSTEM_FLAG) from dual) AS "세부구분" 
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10035',ASK_FLAG) from dual) AS "의뢰구분" 
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10033',ASK_TYPE) from dual) AS "의뢰유형" 
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10037',PROCESS_FLAG) from dual) AS "진행구분" 
    , ASK_YMD AS "의뢰일자"
    , (SELECT NM_KOR FROM A11.A1AT_USER_MASTER WHERE EMPLOYEE_ID = ASK_EMPID) AS "의뢰자"
    , (SELECT NM_KOR FROM A11.A1AT_USER_MASTER WHERE EMPLOYEE_ID = REVIEW_EMPID) AS "검토자"
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10038',REVIEW_FLAG) from dual) AS "검토구분" 
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10034',REVIEW_LEVEL) from dual) AS "검토난이도" 
    , DEV_RECEIVE_YMD AS "개발자접수일자"
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10039',DEV_NAME_CODE) from dual) AS "개발담당자" 
    , WORK_NUM AS "작업번호"
    , ( select A11.PKS_A1_FUNC_01.GET_CODE_ITEM_VALUE('A10042',WORK_STS) from dual) AS "작업상태"
    , WORK_DESC AS "작업내용"
    , TEST_REQ_YMD AS "테스트 요청일"   
    , DEPLOY_REQ_YMD AS "배포요청일"
    , DEPLOY_YMD AS "배포완료일"
    , (SELECT NM_KOR FROM A11.A1AT_USER_MASTER WHERE EMPLOYEE_ID = DEPLOY_CONF_EMPID) AS "배포확인자"
FROM A11.A1CT_ASK A
LEFT JOIN A11.A1CT_REVIEW R
ON A.MAINTER_NUM = R.MAINTER_NUM 
LEFT JOIN A11.A1CT_MAINTER_DETAIL_WORKS W
ON W.MAINTER_NUM = A.MAINTER_NUM
WHERE A.MAINTER_NUM LIKE '202201%';

from dual를 사용하는 것이 사용하지 않는 것과 어떤 차이가 있는 지는 좀 더 알아봐야겠다.

추가로 이렇게 outer join을 할 때는 count 함수를 이용해 값의 정확성을 판단하는 것이 중요하다.

SELECT COUNT(DISTINCT A.MAINTER_NUM) AS TOT_CNT, COUNT(A.MAINTER_NUM) AS A_CNT, COUNT(R.MAINTER_NUM) AS R_CNT, COUNT(W.MAINTER_NUM) AS W_CNT
FROM A11.A1CT_ASK A 
LEFT JOIN A11.A1CT_REVIEW R
ON A.MAINTER_NUM = R.MAINTER_NUM 
LEFT JOIN A11.A1CT_MAINTER_DETAIL_WORKS W
ON W.MAINTER_NUM = A.MAINTER_NUM
WHERE A.MAINTER_NUM LIKE '202209%' ;

W 테이블에서 MAINTER_NUM이 NULL 인 경우가 7개, R 테이블은 4개.
A 테이블에서 MAINTER_NUM이 중복인 경우가 존재한다.

-- OUTER JOIN에서 KEY도 NULL일 수 있다는 것 그리고 중복될 수 있다는 점을 놓치면 안된다.