안녕하세요.

오라클 쿼리를 문의를 드립니다.

저번에 마농님께서 알려주신 쿼리입니다.

SELECT --S_BUYELIST
B.C_CODE C_CODE --조직코드
, B.DEPT_CODE DEPT_CODE --부서코드
, F_TEC_GETDEPTNAME(’2000’,DEPT_CODE) DEPT_CODE_2000 --부서코드 2000
, F_TEC_GETDEPTNAME(’3000’,DEPT_CODE) DEPT_CODE_3000 --부서코드 3000
, COUNT(*) CNT_TOTAL --총건수
, NVL(SUM(A.SUMM_CHAR_AMOU),0) AMT_TOTAL --총공급가액
, COUNT(DECODE(A.MANA_PROC_STAT,’800’,1)) CNT_SIGN --업체인증건수
, NVL(SUM(DECODE(A.MANA_PROC_STAT,’800’,A.SUMM_CHAR_AMOU)),0) AMT_SIGN --업체인증금액
, COUNT(DECODE(A.MANA_PROC_STAT,’200’,1)) CNT_NOTE --수기인증건수
, NVL(SUM(DECODE(A.MANA_PROC_STAT,’200’,A.SUMM_CHAR_AMOU)),0) AMT_NOTE --수기인증금액
, COUNT(DECODE(A.MANA_PROC_STAT,’800’,1,’200’,1)) CNT_SIGN_NOTE --총인증건수
, NVL(SUM(DECODE(A.MANA_PROC_STAT,’800’,A.SUMM_CHAR_AMOU,’200’,A.SUMM_CHAR_AMOU)),0) AMT_SIGN_NOTE --총인증금액
, COUNT(DECODE(B.YN_ACC,’Y’,1)) CNT_NOACC --미인증건수
, NVL(SUM(DECODE(B.YN_ACC,’Y’,A.SUMM_CHAR_AMOU)),0) AMT_NOACC --미인증금액
, COUNT(CASE WHEN A.MANA_PROC_STAT IN (’100’,’550’,’900’) THEN 1 END) CNT_SU_SIGN --회계처리건수
, NVL(SUM(CASE WHEN A.MANA_PROC_STAT IN (’100’,’550’,’900’) THEN A.SUMM_CHAR_AMOU END),0) AMT_SU_SIGN --회계처리금액
, COUNT(DECODE(B.YN_ACC,’N’,1)) CNT_NOSIGN --회계미처리
FROM TEX_MASTER A,
TEX_PRE_MASTER B
WHERE
A.MANA_BILL_NUMB = B.MANA_BILL_NUMB(+)
AND A.HEAD_DOCU_DATE >= ’20080101’ AND A.HEAD_DOCU_DATE <= ’20080131’
AND B.TAX_TYPE = ’1’
 
GROUP BY C_CODE, DEPT_CODE
ORDER BY DEPT_CODE

========================================================================================

제가제일처음에 올린 쿼리입니다.

SELECT
    A.C_CODE
    , A.DEPT_CODE
    , A.DEPT_CODE_2000
    , A.DEPT_CODE_3000
    , NVL(A.CNT_TOTAL, 0), NVL(A.AMT_TOTAL, 0)
    , NVL(B.CNT_SIGN, 0),  NVL(B.AMT_SIGN, 0)
    , NVL(C.CNT_NOTE, 0), NVL(C.AMT_NOTE, 0)
    , NVL(B.CNT_SIGN, 0)+NVL(C.CNT_NOTE, 0)
    , NVL(B.AMT_SIGN, 0)+NVL(C.AMT_NOTE, 0)
    , NVL(D.CNT_ACC, 0), NVL(D.AMT_ACC, 0)
    , NVL(E.CNT_NOTSIGN, 0), NVL(E.AMT_NOTSIGN, 0)
    FROM
    (
        SELECT
        B.C_CODE C_CODE
        , B.DEPT_CODE DEPT_CODE
        , F_TEC_GETDEPTNAME(’2000’ ,B.DEPT_CODE) DEPT_CODE_2000
        , F_TEC_GETDEPTNAME(’3000’ ,B.DEPT_CODE) DEPT_CODE_3000
        , COUNT(*) CNT_TOTAL
        , SUM(A.SUMM_CHAR_AMOU) AMT_TOTAL
        FROM
        TEX_MASTER A,
        TEX_PRE_MASTER B
        WHERE
        A.MANA_BILL_NUMB = B.MANA_BILL_NUMB(+)
        AND A.HEAD_DOCU_DATE >= ’20080101’
        AND A.HEAD_DOCU_DATE <= ’20080131’
        AND B.TAX_TYPE = ’1’
        GROUP BY C_CODE, DEPT_CODE
    ) A,
    (
        SELECT
        B.C_CODE C_CODE
        , B.DEPT_CODE DEPT_CODE
        , COUNT(*) CNT_SIGN
        , SUM(A.SUMM_CHAR_AMOU) AMT_SIGN
        FROM
        TEX_MASTER A,
        TEX_PRE_MASTER B
        WHERE
        A.MANA_BILL_NUMB = B.MANA_BILL_NUMB(+)
        AND A.HEAD_DOCU_DATE >= ’20080101’
        AND A.HEAD_DOCU_DATE <= ’20080131’
        AND B.TAX_TYPE = ’1’
        AND A.MANA_PROC_STAT = ’800’
        GROUP BY C_CODE, DEPT_CODE
    ) B,
    (
        SELECT
        B.C_CODE C_CODE
        , B.DEPT_CODE DEPT_CODE
        , COUNT(*) CNT_NOTE
        , SUM(A.SUMM_CHAR_AMOU) AMT_NOTE
        FROM
        TEX_MASTER A,
        TEX_PRE_MASTER B
        WHERE
        A.MANA_BILL_NUMB = B.MANA_BILL_NUMB(+)
        AND A.HEAD_DOCU_DATE >= ’20080101’
        AND A.HEAD_DOCU_DATE <= ’20080131’
        AND A.MANA_PROC_STAT = ’200’
        AND B.TAX_TYPE = ’1’
        GROUP BY C_CODE, DEPT_CODE
    ) C,
    (
        SELECT
        B.C_CODE C_CODE
        , B.DEPT_CODE DEPT_CODE
        , COUNT(*) CNT_ACC
        , SUM(A.SUMM_CHAR_AMOU) AMT_ACC
        FROM
        TEX_MASTER A,
        TEX_PRE_MASTER B
        WHERE
        A.MANA_BILL_NUMB = B.MANA_BILL_NUMB(+)
        AND A.HEAD_DOCU_DATE >= ’20080101’
        AND A.HEAD_DOCU_DATE <= ’20080131’
        AND B.YN_ACC = ’Y’
        AND B.TAX_TYPE = ’1’
        GROUP BY C_CODE, DEPT_CODE
    ) D,
    (
        SELECT
        B.C_CODE C_CODE
        , B.DEPT_CODE DEPT_CODE
        , COUNT(*) CNT_NOTSIGN
        , SUM(A.SUMM_CHAR_AMOU) AMT_NOTSIGN
        FROM
        TEX_MASTER A,
        TEX_PRE_MASTER B
        WHERE
        A.MANA_BILL_NUMB = B.MANA_BILL_NUMB(+)
        AND A.HEAD_DOCU_DATE >= ’20080101’
        AND A.HEAD_DOCU_DATE <= ’20080131’
        AND B.TAX_TYPE = ’1’
        AND A.MANA_PROC_STAT IN (’100’, ’550’,’900’)
        GROUP BY C_CODE, DEPT_CODE
    ) E
    WHERE
    A.C_CODE = B.C_CODE(+)
    AND A.DEPT_CODE = B.DEPT_CODE(+)
    AND A.C_CODE = C.C_CODE(+)
    AND A.DEPT_CODE = C.DEPT_CODE(+)
    AND A.C_CODE = D.C_CODE(+)
    AND A.DEPT_CODE = D.DEPT_CODE(+)
    AND A.C_CODE = E.C_CODE(+)
    AND A.DEPT_CODE = E.DEPT_CODE(+)
    ORDER BY A.DEPT_CODE

==========================================================================

이 2개의 쿼리는 동일한 쿼리입니다.

그런데. 이상하게 마지막 데이타의 값들은 다르게 뿌려줍니다...ㅠ.ㅠ

그래서. 위의 코드의 DECODE부분에 1로 적은 부분을 0으로 바꾼다면.

SELECT --S_BUYELIST
B.C_CODE C_CODE --조직코드
, B.DEPT_CODE DEPT_CODE --부서코드
, F_TEC_GETDEPTNAME(’2000’,DEPT_CODE) DEPT_CODE_2000 --부서코드 2000
, F_TEC_GETDEPTNAME(’3000’,DEPT_CODE) DEPT_CODE_3000 --부서코드 3000
, COUNT(*) CNT_TOTAL --총건수
, NVL(SUM(A.SUMM_CHAR_AMOU),0) AMT_TOTAL --총공급가액
, COUNT(DECODE(A.MANA_PROC_STAT,’800’,0)) CNT_SIGN --업체인증건수
, NVL(SUM(DECODE(A.MANA_PROC_STAT,’800’,A.SUMM_CHAR_AMOU)),0) AMT_SIGN --업체인증금액
, COUNT(DECODE(A.MANA_PROC_STAT,’200’,0)) CNT_NOTE --수기인증건수
, NVL(SUM(DECODE(A.MANA_PROC_STAT,’200’,A.SUMM_CHAR_AMOU)),0) AMT_NOTE --수기인증금액
, COUNT(DECODE(A.MANA_PROC_STAT,’800’,0,’200’,0)) CNT_SIGN_NOTE --총인증건수
, NVL(SUM(DECODE(A.MANA_PROC_STAT,’800’,A.SUMM_CHAR_AMOU,’200’,A.SUMM_CHAR_AMOU)),0) AMT_SIGN_NOTE --총인증금액
, COUNT(DECODE(B.YN_ACC,’Y’,0)) CNT_NOACC --미인증건수
, NVL(SUM(DECODE(B.YN_ACC,’Y’,A.SUMM_CHAR_AMOU)),0) AMT_NOACC --미인증금액
, COUNT(CASE WHEN A.MANA_PROC_STAT IN (’100’,’550’,’900’) THEN 0 END) CNT_SU_SIGN --회계처리건수
, NVL(SUM(CASE WHEN A.MANA_PROC_STAT IN (’100’,’550’,’900’) THEN A.SUMM_CHAR_AMOU END),0) AMT_SU_SIGN --회계처리금액
, COUNT(DECODE(B.YN_ACC,’N’,0)) CNT_NOSIGN --회계미처리
FROM TEX_MASTER A,
TEX_PRE_MASTER B
WHERE
A.MANA_BILL_NUMB = B.MANA_BILL_NUMB(+)
AND A.HEAD_DOCU_DATE >= ’20080101’ AND A.HEAD_DOCU_DATE <= ’20080131’
AND B.TAX_TYPE = ’1’
 
GROUP BY C_CODE, DEPT_CODE
ORDER BY DEPT_CODE

이쿼리가 맞지 않나요..??

그런데. 마지막 데이타의 값은. 앞의 2개의 행의 값은 똑같지만 나머지지 값들은 1번 쿼리에서는  다 뿌려주지만 2번쿼리는 앞의 2개행만 뿌려주고 나머지는 다 0으로 뿌려줍니다...왜이런지요. 수정했지만.  1번쿼리와 동일하게 뿌려주고요..

 그리고 이 마지막 데이타의 값이. 웹과 토드, 엑셀다운 받았을때 또 값이 다 다르게 보여진다는.........ㅠ.ㅠ

고수님들의 요청을 부탁합니다.