S5A0100200612131 신세계 S2BGWL12312 2006/12/13 50 100 0 0 11 A
S5A0100200612132 신세계 S2BGWL12312 2006/12/13 50 100 0 0 11 A
S5A0100200612133 신세계 S2BGWL12312 2006/12/13 30 100 0 0 11 B
<소계> 130 300 0 0
S5A0300200612131 동양 SDEGWL12312 2006/12/13 50 100 0 0 11 C
S5A0300200612132 동양 SDEGWL12312 2006/12/13 50 0 0 0 11 C
<소계> 100 100 0 0
<합계> 230 400 0 0
Rollup쿼리를 사용할때 그룹핑을 할 조건이 되는 필드를 지정(맞는지 모르겠다..;;)하여,
Group by를 할대와 같이 집계함수를 사용하여 쿼리를 실행하면, 소계가 계산되어 나온다.
SELECT /*+ Dis/XML+DisINOCP007Q/QUERY_CENTER_JOB_TYPE/2006.12.15 */
DECODE(GROUPING(A.ORD_LO_CD),1,'<합계>',DECODE(GROUPING(B.MTR_IN_NO),1,'<소계>', B.MTR_IN_NO)) as MTR_IN_NO,--입고번호
DECODE(GROUPING(B.MTR_IN_NO),1,'',FUNC_DIS_CUSTNM('S', A.ORD_LO_CD)) AS ORD_LO_CD,--발주처
DECODE(GROUPING(B.MTR_IN_NO),1,'',MAX(B.ORD_MTR_CD)) AS ORD_MTR_CD,--발주번호
DECODE(GROUPING(B.MTR_IN_NO),1,'',MAX(TO_CHAR(TO_DATE(B.YMD, 'YYYYMMDD'), 'YYYY/MM/DD'))) AS YMD,--입고일자
SUM(B.MTRIN_QTY) AS MTRIN_QTY,--자재입고수량
SUM(A.ORD_QTY) AS ORD_QTY,--발주수량
SUM(B.PRCH_QTY) AS PRCH_QTY,--매입수량
SUM(B.PRCH_AMT) AS PRCH_AMT,--매입금액
DECODE(GROUPING(B.MTR_IN_NO),1,'',MAX(B.MXDRT_CD)) AS MXDRT_CD,--혼용률
DECODE(GROUPING(B.MTR_IN_NO),1,'',MAX(B.SHADE_CD)) AS SHADE_CD--SHADE
FROM
SDSORDH A, SDSIND B
WHERE
A.ORD_MTR_CD=B.ORD_MTR_CD AND A.ORD_SEQ=B.ORD_SEQ
GROUP BY
ROLLUP(A.ORD_LO_CD, B.MTR_IN_NO)
[출처] http://cafe.naver.com/leesc81.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=120
