/* benit.jd21.model.EbizCalcProc.select.detail : 조회 */
SELECT MAKE_YYMM ,
       MAKE_DT as makeDt ,
       SHOP_ID as shopId ,
       shopNm as shopNm ,
       PRDT_CD as prdtCd ,
       COLOR_CD as colorCd ,
       SIZE_CD as sizeCd ,
       SUM(SCM_QTY) as scmQty ,
       SUM(SCM_AMT) as scmAmt ,
       SUM(ERP_QTY) as erpQty ,
       SUM(ERP_AMT) as erpAmt ,
       SUM(SCM_QTY)-SUM(ERP_QTY) as calcQty ,
       SUM(SCM_AMT)-SUM(ERP_AMT) as calcAmt ,
       CLOSE_YN as closeYn ,
       REG_NO ,
       procMonth ,
       procDay
  FROM (

            SELECT SUBSTR(MAKE_DT, 1, 6) MAKE_YYMM ,
                   MAKE_DT ,
                   SHOP_ID ,
                   (SELECT SHOP_NM
                      FROM T_SHOP
                     WHERE SHOP_ID = A.SHOP_ID) as shopNm ,
                   PRDT_CD ,
                   COLOR_CD ,
                   SIZE_CD ,
                   SUM(QTY) SCM_QTY ,
                   SUM(SALE_PRC) SCM_AMT ,
                   0 ERP_QTY ,
                   0 ERP_AMT ,
                   CLOSE_YN ,
                   REG_NO ,
                   SUBSTR(TO_CHAR(A.WORK_DT, 'YYYYMMDD'), 0, 6) AS procMonth ,
                   SUBSTR(TO_CHAR(A.WORK_DT, 'YYYYMMDD'), 7, 2) AS procDay
              FROM T_EBIZ_SCM_UPLOAD A
             WHERE substr(MAKE_DT, 1, 6) BETWEEN '201705' /**P*/ AND '201706' /**P*/
                     AND MAKE_DT  = '20170508'
                      AND SHOP_ID  = 'S51001'
                      AND PRDT_CD  = 'SWWBLF61090'
                      AND COLOR_CD = 'IV'
                      AND SIZE_CD  = '66' 
             GROUP BY SHOP_ID ,
                   MAKE_DT ,
                   PRDT_CD ,
                   COLOR_CD ,
                   SIZE_CD ,
                   SUBSTR(MAKE_DT, 1, 6) ,
                   CLOSE_YN ,
                   REG_NO ,
                   SUBSTR(TO_CHAR(A.WORK_DT, 'YYYYMMDD'), 0, 6) ,
                   SUBSTR(TO_CHAR(A.WORK_DT, 'YYYYMMDD'), 7, 2)
                                      
             UNION ALL

            SELECT SUBSTR(MAKE_DT, 1, 6) MAKE_YYMM ,
                   MAKE_DT ,
                   SHOP_ID ,
                   (SELECT SHOP_NM
                      FROM T_SHOP
                     WHERE SHOP_ID = B.SHOP_ID) as shopNm ,
                   PRDT_CD ,
                   COLOR_CD ,
                   SIZE_CD ,
                   0 SCM_QTY ,
                   0 SCM_AMT ,
                   SUM(DECODE(RET_YN, 'Y', -1*QTY, QTY)) ERP_QTY ,
                   SUM(DECODE(RET_YN, 'Y', -1*REAL_SALE_AMT, REAL_SALE_AMT)) ERP_AMT ,
                   '' CLOSE_YN ,
                   '' REG_NO ,
                   '' procMonth ,
                   '' procDay
              FROM T_SHOP_RNDS_BASE_EBIZ_TEST B
             WHERE substr(MAKE_DT, 1, 6) BETWEEN '201705' /**P*/ AND '201706' /**P*/
               AND MAKE_DT  = '20170508'
               AND SHOP_ID  = 'S51001'
               AND PRDT_CD  = 'SWWBLF61090'
               AND COLOR_CD = 'IV'
               AND SIZE_CD  = '66' 
               AND DEL_DAY IS NULL
               AND STOCK_STAT = 'C20922'
               AND SUBSTR(SHOP_ID, 1, 2) = 'S5'
             GROUP BY SUBSTR(MAKE_DT, 1, 6) ,
                   MAKE_DT ,
                   SHOP_ID ,
                   PRDT_CD ,
                   COLOR_CD ,
                   SIZE_CD 
)
 WHERE 1=1
 GROUP BY MAKE_YYMM ,
       MAKE_DT ,
       SHOP_ID,
       shopNm ,
       PRDT_CD ,
       COLOR_CD ,
       SIZE_CD ,
       CLOSE_YN ,
       REG_NO ,
       procMonth ,
       procDay
 ORDER BY CLOSE_YN,
       SHOP_ID                    

위와 같은 쿼리를 이용하면

cap1(첨부파일)과 같은 결과가 나옵니다. 

제가 원하는 결과값은 

CAP2 (첨부파일 엑셀 캡쳐)와 같은 건데 

원하는 값이 나오질 않습니다.

-------------------------------------------------

0 SCMQTY

0 SCMAMT 

에서 0으로 넣는것은 데이터가 있는것이라서 UNION ALL이 적용되지 않는것인지?

SUM(SCM_QTY)-SUM(ERP_QTY) as calcQty

, SUM(SCM_AMT)-SUM(ERP_AMT) as calcAmt 

아니면 위 부분때문에 값이 두개가 나오는 것인지 .. 

이해가 가질 않아서 질문 올립니다. 

감사합니다!