[phil-story] Always Fighting 

'쿼리'에 해당되는 글 7건

  1. 2008.04.14 [오라클] 소계 및 합계 내는 쿼리 모음_03
IT_Expert/DataBase | Posted by 낫기법필 2008. 4. 14. 13:21

[오라클] 소계 및 합계 내는 쿼리 모음_03

안녕하세요 다시한번 쿼리 급질문 합니다

내용을 이렇게 나와야 합니다.

작업일자       작업지시번호    모델      공정라인     대기수량 미완공수량  완공수량

2006/10/01                                          2-1

10/02                                                 2-1

                                                        라인별 합계


                                                        2-2

                                                        2-2


                                                        라인별 합계

                                                         전체합계



이런 식으로 나와야 하는데 .. 지금 공정라인쪽 항목이 라인 번호 나오고 라인별 합계는 그 뒤로 나오고 전체합계가 또 그 뒤로 나옵니다.

라인이 끝나면 라인별 합계가 나와야 하는데 ....


제가 만든 쿼리는 좀 복잡하지만 이렇게 만들었습니다


 SELECT  Z.SEQ

       , Z.WORK_DATE

       , Z.WIP_ENTITY_NAME AS WIP_ENTITY_NAME 

       , DECODE(Z.ROUTENAME,'라인별 소계 ',Z.ROUTENAME,

    (SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = Z.ROUTENAME )) AS ROUTENAME                        

    , Z.MATERIALNAME

    , Z.LPL_ITEM_CODE

    , Z.MODEL 

      , Z.WAIT_QTY

       , Z.PROCESS_QTY

       , Z.CLOSE_QTY

       , Z.START_QTY

  FROM

       (

      SELECT 1 SEQ,

   D.WORK_DATE

       , D.WIP_ENTITY_NAME AS WIP_ENTITY_NAME 

       , D.ROUTENAME

       , D.MATERIALNAME

    , D.LPL_ITEM_CODE

    , D.MODEL 

      , D.WAIT_QTY

       , D.PROCESS_QTY

       , D.CLOSE_QTY

       , D.START_QTY

 FROM

     (

  SELECT

   SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) AS WORK_DATE

        ,B.WIP_ENTITY_NAME AS WIP_ENTITY_NAME 

        ,(SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) AS ROUTENAME

        ,A.MATERIALNAME

     ,C.LPL_ITEM_CODE

     ,(SELECT MODEL FROM MATERIAL WHERE  MATERIALNAME = A.MATERIALNAME AND MATERIALVERSION = A.MATERIALVERSION ) AS MODEL 

       ,SUM(DECODE(WORKSTATE,'01', A.CURRQTY,0)) AS WAIT_QTY

        ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY,0)) AS PROCESS_QTY

        ,SUM(DECODE(WORKSTATE,'05', A.CURRQTY,0)) AS CLOSE_QTY

        ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY,0)) AS START_QTY   

     FROM CT_LOT_MAST A 

       , CT_WORKINST B

       , MATERIAL C

   WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID    

     AND LOTID LIKE (SELECT X.WC_CODE 

                       FROM CT_CODE_TYPE X

                      WHERE TYPE_CODE = 'B2'

                        AND TYPE_DIV  = 'R') || '%' 

     AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'

     AND C.MATERIALNAME = A.MATERIALNAME

  AND C.MATERIALVERSION = A.MATERIALVERSION

   GROUP BY

     A.MATERIALNAME, A.MATERIALVERSION, A.ROUTENAME, A.ROUTEVERSION,C.LPL_ITEM_CODE  

       ,SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) 

       ,B.WIP_ENTITY_NAME)D

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

    UNION ALL

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

       SELECT 2 AS SEQ,

   D.WORK_DATE

       , D.WIP_ENTITY_NAME AS WIP_ENTITY_NAME 

       , '라인별 소계 'AS ROUTENAME

       , D.MATERIALNAME

    , D.LPL_ITEM_CODE

    , D.MODEL 

      , D.WAIT_QTY

       , D.PROCESS_QTY

       , D.CLOSE_QTY

       , D.START_QTY

 FROM

     (

  SELECT

   SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) AS WORK_DATE

        ,B.WIP_ENTITY_NAME AS WIP_ENTITY_NAME 

        ,(SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) AS ROUTENAME

        ,A.MATERIALNAME

     ,C.LPL_ITEM_CODE

     ,(SELECT MODEL FROM MATERIAL WHERE  MATERIALNAME = A.MATERIALNAME AND MATERIALVERSION = A.MATERIALVERSION ) AS MODEL 

       ,SUM(DECODE(WORKSTATE,'01', A.CURRQTY,0)) AS WAIT_QTY

        ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY,0)) AS PROCESS_QTY

        ,SUM(DECODE(WORKSTATE,'05', A.CURRQTY,0)) AS CLOSE_QTY

        ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY,0)) AS START_QTY   

     FROM CT_LOT_MAST A 

       , CT_WORKINST B

       , MATERIAL C

   WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID    

     AND LOTID LIKE (SELECT X.WC_CODE 

                       FROM CT_CODE_TYPE X

                      WHERE TYPE_CODE = 'B2'

                        AND TYPE_DIV  = 'R') || '%' 

     AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'

     AND C.MATERIALNAME = A.MATERIALNAME

  AND C.MATERIALVERSION = A.MATERIALVERSION

   GROUP BY

     A.MATERIALNAME, A.MATERIALVERSION, A.ROUTENAME, A.ROUTEVERSION,C.LPL_ITEM_CODE  

       ,SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) 

       ,B.WIP_ENTITY_NAME)D

        

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

    UNION ALL

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

       SELECT 3 AS SEQ,

   D.WORK_DATE

       , D.WIP_ENTITY_NAME AS WIP_ENTITY_NAME 

       , '전체합계 ' AS ROUTENAME

       , D.MATERIALNAME

    , D.LPL_ITEM_CODE

    , D.MODEL 

      , D.WAIT_QTY

       , D.PROCESS_QTY

       , D.CLOSE_QTY

       , D.START_QTY

 FROM

     (

  SELECT

   SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) AS WORK_DATE

        ,B.WIP_ENTITY_NAME AS WIP_ENTITY_NAME 

        ,(SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) AS ROUTENAME

        ,A.MATERIALNAME

     ,C.LPL_ITEM_CODE

     ,(SELECT MODEL FROM MATERIAL WHERE  MATERIALNAME = A.MATERIALNAME AND MATERIALVERSION = A.MATERIALVERSION ) AS MODEL 

       ,SUM(DECODE(WORKSTATE,'01', A.CURRQTY,0)) AS WAIT_QTY

        ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY,0)) AS PROCESS_QTY

        ,SUM(DECODE(WORKSTATE,'05', A.CURRQTY,0)) AS CLOSE_QTY

        ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY,0)) AS START_QTY   

     FROM CT_LOT_MAST A 

       , CT_WORKINST B

       , MATERIAL C

   WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID    

     AND LOTID LIKE (SELECT X.WC_CODE 

                       FROM CT_CODE_TYPE X

                      WHERE TYPE_CODE = 'B2'

                        AND TYPE_DIV  = 'R') || '%' 

     AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'

     AND C.MATERIALNAME = A.MATERIALNAME

  AND C.MATERIALVERSION = A.MATERIALVERSION

   GROUP BY

     A.MATERIALNAME, A.MATERIALVERSION, A.ROUTENAME, A.ROUTEVERSION,C.LPL_ITEM_CODE  

       ,SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) 

       ,B.WIP_ENTITY_NAME)D)Z 


   order by 4,3,2,1



좀 복잡하죠 ...


쉽게 설명하면 라인이 끝나고 고 라인에 대한 라인별 합계 그리고 라인번호 라인별 합계 ..... 모든 라인이 끝나면 최종적으로 전체합계가 나와야 하는데 ㅠㅠ


어캐 방법이 없을까요 ?


이 글에 대한 댓글이 총 4건 있습니다.
이게 맞는지는 모르겠네요

SELECT DECODE(B.SEQ,1,A.WORK_DATE) WORK_DATE,
       DECODE(B.SEQ,1,A.WIP_ENTITY_NAME) WIP_ENTITY_NAME,
       DECODE(B.SEQ,1,A.MODEL) MODEL,
       DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'라인 합계','전체합계') ROUTENAME,
       DECODE(B.SEQ,1,A.MATERIALNAME) MATERIALNAME,
       DECODE(B.SEQ,1,A.LPL_ITEM_CODE) LPL_ITEM_CODE,
       NVL(SUM(A.WAIT_QTY),0) WAIT_QTY
       NVL(SUM(A.PROCESS_QTY),0) PROCESS_QTY,
       NVL(SUM(A.CLOSE_QTY),0) CLOSE_QTY,
       NVL(SUM(A.START_QTY),0) START_QTY
  FROM (SELECT LEVEL SEQ
          FROM DUAL
        CONNECT BY LEVEL <= 3) B,
       (SELECT SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) WORK_DATE,
        B.WIP_ENTITY_NAME,
        (SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) ROUTENAME,
        A.MATERIALNAME,
        C.LPL_ITEM_CODE,
        C.MODEL,
        SUM(DECODE(WORKSTATE,'01', A.CURRQTY)) WAIT_QTY,
        SUM(DECODE(WORKSTATE,'02', A.CURRQTY)) PROCESS_QTY,
        SUM(DECODE(WORKSTATE,'05', A.CURRQTY)) CLOSE_QTY,
        SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY)) START_QTY
   FROM CT_LOT_MAST A, CT_WORKINST B, MATERIAL C
  WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID
    AND LOTID LIKE (SELECT X.WC_CODE
        FROM CT_CODE_TYPE X
       WHERE TYPE_CODE = 'B2'
         AND TYPE_DIV  = 'R') || '%'
    AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'
    AND C.MATERIALNAME = A.MATERIALNAME
    AND C.MATERIALVERSION = A.MATERIALVERSION
    GROUP BY SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2),
             B.WIP_ENTITY_NAME,A.ROUTENAME,A.ROUTEVERSION,A.MATERIALNAME,C.LPL_ITEM_CODE,C.MODEL) A
 GROUP BY DECODE(B.SEQ,1,A.WORK_DATE),
          DECODE(B.SEQ,1,A.WIP_ENTITY_NAME),
          DECODE(B.SEQ,1,A.MODEL),
          DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'라인 합계','전체합계'),
          DECODE(B.SEQ,1,A.MATERIALNAME),
          DECODE(B.SEQ,1,A.LPL_ITEM_CODE)
 ORDER BY 4,3,2,1
풍차님이 2006-10-30 13:22:27에 작성한 댓글입니다.
이 댓글은 2006-10-30 13:54:31에 마지막으로 수정되었습니다. Edit Ｘ

감사합니다

그런데 소계가

1-1 파트 소계

이렇게 나오는데

라인별 소게 이렇게 하는 법은 없는지 ..

서정대님이 2006-10-30 15:33:08에 작성한 댓글입니다. Edit Ｘ

SELECT DECODE(B.SEQ,1,A.WORK_DATE) WORK_DATE,
       DECODE(B.SEQ,1,A.WIP_ENTITY_NAME) WIP_ENTITY_NAME,
       DECODE(B.SEQ,1,A.MODEL) MODEL,
       DECODE(B.SEQ,1,A.ROUTENAME,2,'라인별 합계','전체합계') ROUTENAME,
       DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'X') ROUTENAME1, --Sort용
       DECODE(B.SEQ,1,A.MATERIALNAME) MATERIALNAME,
       DECODE(B.SEQ,1,A.LPL_ITEM_CODE) LPL_ITEM_CODE,
       NVL(SUM(A.WAIT_QTY),0) WAIT_QTY,
       NVL(SUM(A.PROCESS_QTY),0) PROCESS_QTY,
       NVL(SUM(A.CLOSE_QTY),0) CLOSE_QTY,
       NVL(SUM(A.START_QTY),0) START_QTY
  FROM (SELECT LEVEL SEQ
          FROM DUAL
        CONNECT BY LEVEL <= 3) B,
       (SELECT SUBSTR(A.MODIFY_DATE ,1,4)|| '/' ||SUBSTR(A.MODIFY_DATE,5,2)|| '/' ||SUBSTR(A.MODIFY_DATE,7,2) WORK_DATE,
               B.WIP_ENTITY_NAME,
               B.DESCRIPTION ROUTENAME,
               A.MATERIALNAME,
               C.LPL_ITEM_CODE,
               C.MODEL,
               SUM(DECODE(WORKSTATE,'01', A.CURRQTY)) WAIT_QTY,
               SUM(DECODE(WORKSTATE,'02', A.CURRQTY)) PROCESS_QTY,
               SUM(DECODE(WORKSTATE,'05', A.CURRQTY)) CLOSE_QTY,
               SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY)) START_QTY
          FROM D.DESCRIPTION, CT_LOT_MAST A, CT_WORKINST B, MATERIAL C
         WHERE D.ROUTENAME = A.ROUTENAME
           AND D.ROUTEVERSION = A.ROUTEVERSION
           AND A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID
           AND A.LOTID LIKE (SELECT X.WC_CODE
                               FROM CT_CODE_TYPE X
                              WHERE TYPE_CODE = 'B2'
                                AND TYPE_DIV  = 'R') || '%'
           AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'
           AND C.MATERIALNAME = A.MATERIALNAME
           AND C.MATERIALVERSION = A.MATERIALVERSION
           GROUP BY SUBSTR(A.MODIFY_DATE ,1,4)|| '/' ||SUBSTR(A.MODIFY_DATE,5,2)|| '/' ||SUBSTR(A.MODIFY_DATE,7,2),
                    B.WIP_ENTITY_NAME,B.DESCRIPTION,A.MATERIALNAME,C.LPL_ITEM_CODE,C.MODEL) A
 GROUP BY DECODE(B.SEQ,1,A.WORK_DATE),DECODE(B.SEQ,1,A.WIP_ENTITY_NAME),
          DECODE(B.SEQ,1,A.MODEL),
          DECODE(B.SEQ,1,A.ROUTENAME,2,'라인별 합계','전체합계'),
          DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'X'),
          DECODE(B.SEQ,1,A.MATERIALNAME),DECODE(B.SEQ,1,A.LPL_ITEM_CODE)
 ORDER BY 5,3,2,1
풍차님이 2006-10-30 16:43:03에 작성한 댓글입니다. Edit Ｘ

풍차님 감사합니다.

서정대님이 2006-10-30 18:09:42에 작성한 댓글입니다. Edit Ｘ

티스토리툴바