h2.2.1 개념 및 특징
h2.2.2 양쪽 OUTER조인의 해결
SELECT 년월, nvl(sum(불입금액),0), nvl(sum(인출금액),0)
FROM ( SELECT 관리번호,
substr(불입일자,1,6) 년월,
원화금액 불입금액,
to_number(null) 인출금액
FROM 예입내역
WHERE 불입일자 between :in_date and :end_date
UNION ALL
SELECT 관리번호,
substr(인출일자,1,6) 년월,
to_number(null) 불입금액,
원화금액 인출금액
FROM 인출내역
WHERE 인출일자 between :in_date and :end_date )
GROUP BY 년월 ;
h2.2.3 특이한 활용사례
h3.2.3.1 이종(異種)로우들의 고정양식 출력
SELECT sum(tot*decode(NO-LINE,1,-1,3,-1,1) ) tot,
sum(W01*decode(NO-LINE,1,-1,3,-1,1)), ...........................
FROM ( SELECT LINE, sum(AMT) TOT,
sum(decode(MM,'01',AMT)) W01, ........................
FROM ( SELECT y.NO LINE, MM,
sum(AMT*decode(y.NO*LINE,6,-1,1)) AMT-----------------------②
FROM (SELECT '1' LINE, substr(년월,5,2) MM,sum(AMT) AMT
FROM 매출집계
WHERE 년월 LIKE '1997%'
GROUP BY substr(년월,5,2)
UNION ALL
SELECT '2' LINE, substr(년월,5,2) MM,sum(AMT) AMT
FROM 매출원가
WHERE 년월 LIKE '1997%'
GROUP BY substr(년월,5,2) ) x, COPY_T y
WHERE y.NO IN (LINE, 3)---------------------①
GROUP BY y.NO, MM---------------------------③
UNION ALL
SELECT y.NO LINE, MM,SUM(AMT*decode(y.NO*LINE,88,-1,1) ) AMT
FROM (SELECT decode(substr(계정,1,2),'21', 13,substr(계정,2,2)+1) LINE,
substr(일자,5,2) MM,
SUM(AMT) AMT
FROM 전표집계
WHERE 일자 LIKE '1997%'
AND 계정 BETWEEN '203' AND '219'
GROUP BY decode(substr(계정,1,2),'21', 13,substr(계정,2,2)+1),
substr(일자,5,2) ) x, COPY_T y
WHERE y.NO IN ( LINE,decode(LINE,13,NULL,11) )
GROUP BY y.NO, MM )
GROUP BY LINE ) x, COPY_T y
WHERE y.NO IN (LINE, decode(LINE,3,12, 11,12), decode(LINE,3,14,11,14,13,14))
GROUP BY y.NO ;
h3.2.3.2 전후(前後)간의 로우 비교
SELECT rpad(월,2)||' 월' 구분,
sum(당월) 당월,
sum(당월 - 전월) 증감액,
sum(decode(당월,0,null,(당월-전월)*100 / 당월)) 증감율
FROM ( SELECT decode(NO, 2, MM+1, MM+0) 월,
nvl(sum(decode(NO,1,AMT)), 0) 당월,
nvl(sum(decode(NO,2,AMT)), 0) 전월
FROM ( SELECT decode(년월, '199612', '00',substr(년월,5,2)) MM,
sum(AMT) AMT
FROM 매출집계
WHERE 사업장 = '1공장'
AND 년월 between '199612' and '199712'
GROUP BY decode(년월, '199612', '00',substr(년월,5,2))) x,
COPY_T y
WHERE NO between decode(MM,'00',2,1) and decode(MM,'12',1,2)
GROUP BY decode( NO, 2, MM+1, MM+0 )
GROUP BY 월 ;
h3.2.3.2 추출컬럼의 특이한 가공
SELECT 품목,
Decode(NO,1,'검사',2,'합격','율(%)'),
Sum(decode(주,0,cnt)),
Decode(no,3, round(sum(분자)*100/sum(분모),3), sum(분자)+sum(분모)),
Sum(decode(주,1,건수)), Sum(decode(주,2,건수)), Sum(decode(주,3,건수)),
Sum(decode(주,4,건수)), Sum(decode(주,5,건수)), Sum(decode(주,6,건수))
FROM ( SELECT 품목, NO, 주,
Decode(NO, 3, round(sum(합격수)*100/sum(검사수),3),
sum(합격수) + sum(검사수)) 건수,
Sum(decode(주, 0,null, 검사수)) 분모,
Sum(decode(주, 0,null, 합격수)) 분자
FROM (SELECT 품목,
Decode(to_char(검사일,'yyyymm'), '199712', '0',
ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1)/7)) 주,
Count(*) 검사수,
0 합격수,
1 SW
FROM 품질검사
WHERE 공정 = 'PRESS'
and 검사일 between '01-DEC-97' and '31-JAN-98'
GROUP BY 품목,
Decode(to_char(검사일,'yyyymm'), '199712', '0',
ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1)/7))
UNION ALL
SELECT 품목,
Decode(to_char(검사일,'yyyymm'), '199712', '0',
ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1)/7)) 주,
0 검사수,
Count(합격항목수) 합격수,
2 SW
FROM ( SELECT 품목, 검사일,
Count(*) 검사항목수,
Sum(decode(LEAST(grade,'C'),'C',1)) 합격항목수
FROM 검사결과
WHERE 공정 = 'PRESS'
and 검사일 between '01-DEC-97' and '31-JAN-98'
GROUP BY 품목, 공정, 검사일, 일련번호 )
WHERE (검사항목수 = 합격항목수) or (합격항목수 is null and rownum = 1)
GROUP BY 품목,
Decode(to_char(검사일,'yyyymm'), '199712', '0',
ceil((to_char(검사일,'dd')+to_char(trunc(검사일,'mm'),'d')-1) / 7))
) x, COPY_T y
WHERE NO in (SW, 3) and NO <= 3
GROUP BY 품목, NO, 주 )
GROUP BY 품목, NO ;