4개의 회사의 각 인터페이스 TOTAL_ROWS를 한 쿼리로 만들어보자. (SQL 행을 열로 변환!!!)
1. 한 회사의 인터페이스 이름과, TOTAL_ROWS를 구하였다. (한 회사별로 인터페이스 종류는 총 9개)
[결과]
2. 위에서 썼던 쿼리를 COMPANY_CODE만 바꿔주어 UNION ALL로 묶어주었다. 총 39 ROWS가 조회됨.
내가 뽑고자 하는 쿼리는 행으로 나열된 TOTAL_ROWS가 열로 보여지게끔 변환하는 것이기 때문에 3번과 같이 DECODE를 이용하였다.
[결과]
3. IF_NAME은 모두 같고, 이들 중 다른 것은 COMPANY_CODE이므로 COMPANY_CODE를 기준으로 DECODE 함수를 이용한다.
IF_NAME은 중복임. 이 때 GROUP BY를 이용하여 'IF_NAME을 묶어줘야지~' 라고 생각할 수 있음
SELECT IF_NAME,
DECODE(COMPANY_CODE, '1200', TOTAL_ROWS) AS TOTAL1200,
DECODE(COMPANY_CODE, '1400', TOTAL_ROWS) AS TOTAL1400,
DECODE(COMPANY_CODE, '1600', TOTAL_ROWS) AS TOTAL1600,
DECODE(COMPANY_CODE, '1300', TOTAL_ROWS) AS TOTAL1300
FROM (SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1200'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1200')
union all
SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1400'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1400')
union all
SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1600'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1600')
union all
SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1300'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1300'));
[결과]
4. 위에서 설명한대로.. IF_NAME을 GROUP BY 해주고, 나머지 값들은 함수 사용해서 하나씩만 조회될 수 있도록 수정
SELECT IF_NAME,
TO_CHAR(MAX(DECODE(COMPANY_CODE, '1200', TOTAL_ROWS)), '999,999,999') AS TOTAL1200,
TO_CHAR(MAX(DECODE(COMPANY_CODE, '1400', TOTAL_ROWS)), '999,999,999') AS TOTAL1400,
TO_CHAR(MAX(DECODE(COMPANY_CODE, '1600', TOTAL_ROWS)), '999,999,999') AS TOTAL1600,
TO_CHAR(MAX(DECODE(COMPANY_CODE, '1300', TOTAL_ROWS)), '999,999,999') AS TOTAL1300,
MAX(INTG_INTERFACE_TRANS_ID) AS INTG_INTERFACE_TRANS_ID
FROM (SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE,
IIT.INTG_INTERFACE_TRANS_ID
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1200'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1200')
union all
SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE,
IIT.INTG_INTERFACE_TRANS_ID
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1400'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1400')
union all
SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE,
IIT.INTG_INTERFACE_TRANS_ID
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1600'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1600')
union all
SELECT IRM.IF_NAME,
IIT.TOTAL_ROWS,
IIT.IF_PARAM,
IIT.COMPANY_CODE,
IIT.INTG_INTERFACE_TRANS_ID
FROM INTG_INTERFACE_TRANS IIT, INTERFACE_ITEM_MST IRM, MESSAGE MS
WHERE IIT.IF_CODE = IRM.IF_CODE
AND IIT.COMPANY_CODE = '1300'
AND IIT.COMPANY_CODE = IRM.COMPANY_CODE
AND TO_CHAR(IIT.TRANS_DATE, 'yyyymmdd') BETWEEN '20160412' AND '20160431'
AND IIT.SCHEDULE_CODE = 'MONTHLY_BATCH'
AND 'TXT_' || IIT.ERROR_MESSAGE = MS.MESSAGE_NAME(+)
AND IIT.IF_CODE IN (SELECT if_code
FROM interface_schedule_mapping
WHERE schedule_code = 'MONTHLY_BATCH'
AND company_code = '1300'))
GROUP BY IF_NAME
ORDER BY INTG_INTERFACE_TRANS_ID DESC;
[결과]
** 팁 **
- 3자리 콤마 찍고자 할 때: TO_CHAR( XXX, '999,999,999,999')
- SELECT 1 + NULL FROM DUAL; -------> 값이 뭐가 나올까? NULL 이 나옴.
정리: 숫자 + NULL = NULL
문자 + NULL = NULL
- GROUP BY를 IF_NAME으로 묶어 주면, 나머지 것들을 함수의 형태로 묶어주는데
이 때.. MAX, MIN, SUM 함수 무얼쓰던 상관 없음. 왜냐.. MAX나 MIN은 NULL과 비교하지 않으므로.. 하나의 값만 나옴. 그리고 SUM은 NULL 값을 제외한 합을 구하기 때문에.. 위에서 정리했던 "숫자 + NULL = NULL"이 조회되는 것 과는 상관 없음