(스크랩)ORA-01476 제수가 0 입니다

통계작업에서 종종 ORA-01476 제수가 0 입니다 를 가끔 보게 되는데 나누기에서
분모가 0이어서 발생하는 문제죠.
분모가 0이 안되게 하는게 정답이지만 데이터 정합성과 기타 문제로 인하여 분모가 0이
될 수가 있습니다.
그래서 오류없이 자료를 뽑고, 분모가 0인 데이터는 0으로 표시하기 위해서 아래와
같이 SQL를 작성하시면 됩니다.

SELECT NVL(분자 / DECODE(분모, 0, NULL, 분모), 0) FROM 테이블;

WITH V_SPOT_DATA AS (
            SELECT                        
                A.SPOT_CODE
            ,   A.SPOT_NAME
            ,   U.UNIT_CODE
            ,   L.AIRPORT_NAME                    
            FROM    ANMS_AUTO_SPOT A
            ,       ENMS_UNIT U
            ,       ANMS_AIRPORT_CODE L 
            WHERE
                U.SPOT_CODE  = A.SPOT_CODE
            AND     
                A.AIRPORT_CODE = L.AIRPORT_CODE
            AND     
                U.UNIT_GUBUN = 'CMM1202'
        ) ,V_GROUP_DATA AS ( 
            SELECT
                SUBSTR(MEAS_MONTH , 5 , 6) AS MEAS_MONTH
                ,   SUBSTR(MEAS_MONTH, 0, 4) AS MEAS_YEAR
                ,   WECPNL
                ,   UNIT_CODE
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '01', WECPNL) AS DL01
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '02', WECPNL) AS DL02
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '03', WECPNL) AS DL03
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '04', WECPNL) AS DL04
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '05', WECPNL) AS DL05
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '06', WECPNL) AS DL06
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '07', WECPNL) AS DL07
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '08', WECPNL) AS DL08
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '09', WECPNL) AS DL09
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '10', WECPNL) AS DL10
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '11', WECPNL) AS DL11
                ,   DECODE(SUBSTR(MEAS_MONTH , 5 , 6), '12', WECPNL) AS DL12
            FROM 
                ANMS_MONTH_CONFIRM_DATA 
            WHERE                 
                SUBSTR(MEAS_MONTH , 0 , 4) = 2008 
            AND
                MEAS_MONTH <= (SELECT CONFIRM_DATE FROM WNMS_OPEN_DATE WHERE SPOT_GUBUN = 'CMM1603')                    
            )
            , V_GROUP_U_DATA AS ( 
                SELECT
                    MEAS_YEAR
                    ,   UNIT_CODE
                    ,   MAX(DL01) AS DL01
                    ,   MAX(DL02) AS DL02
                    ,   MAX(DL03) AS DL03
                    ,   MAX(DL04) AS DL04
                    ,   MAX(DL05) AS DL05
                    ,   MAX(DL06) AS DL06
                    ,   MAX(DL07) AS DL07
                    ,   MAX(DL08) AS DL08
                    ,   MAX(DL09) AS DL09
                    ,   MAX(DL10) AS DL10
                    ,   MAX(DL11) AS DL11
                    ,   MAX(DL12) AS DL12
                    ,   SUM(WECPNL) AS MONTH_SUM
                    ,   COUNT(WECPNL) AS MONTH_CNT
                    ,   10 * LOG( 10, NVL(1 / DECODE(COUNT(WECPNL), 0, NULL, COUNT(WECPNL)),0) * SUM(POWER( 10, WECPNL * 0.1))) AS YEAR_LEQ
                FROM 
                    V_GROUP_DATA
                GROUP BY
                    UNIT_CODE , MEAS_YEAR
            )
            SELECT
                *    
            FROM (
                SELECT
                    A.*
                    ,   ROWNUM AS RNUM
                FROM (
                    SELECT
                        MEAS_YEAR
                        ,   AIRPORT_NAME
                        ,   SPOT_NAME
                        ,   TO_CHAR(DL01, '990.00') AS DL01
                        ,   TO_CHAR(DL02, '990.00') AS DL02
                        ,   TO_CHAR(DL03, '990.00') AS DL03
                        ,   TO_CHAR(DL04, '990.00') AS DL04
                        ,   TO_CHAR(DL05, '990.00') AS DL05
                        ,   TO_CHAR(DL06, '990.00') AS DL06
                        ,   TO_CHAR(DL07, '990.00') AS DL07
                        ,   TO_CHAR(DL08, '990.00') AS DL08
                        ,   TO_CHAR(DL09, '990.00') AS DL09
                        ,   TO_CHAR(DL10, '990.00') AS DL10
                        ,   TO_CHAR(DL11, '990.00') AS DL11
                        ,   TO_CHAR(DL12, '990.00') AS DL12
                        ,   TO_CHAR(trunc(round(YEAR_LEQ,2),1), '990.0') YEAR_LEQ
                        ,   MONTH_SUM
                        ,   MONTH_CNT
                    FROM
                        V_GROUP_U_DATA VGD
                        ,   V_SPOT_DATA VSD
                    WHERE
                        VGD.UNIT_CODE = VSD.UNIT_CODE  
                    ORDER BY
                        AIRPORT_NAME, SPOT_NAME
                )  A  
            )            
                
             
 
    
    
    
    

출처: http://dev4u.tistory.com/211 [dev for you]