Обработка ошибки из-за деления на ноль с использованием SQL

Ядро базы данных представляет собой финансовую программу под названием Paprika. Его цель - получить доход в декабре 2018 года для конкретного отдела.

(Декабрь 2018 г. стоимость проекта по 3 базам данных (Великобритания, США, БР),пробная занятость / курс валют)(часы работы кафедры на основе оценки / общее количество часов по базам данных)

Нулевые значения берутся из последней части (последние 3 деления) - при выводе часов по классам для каждого проекта у многих из них есть ноль часов.

Ниже приведены комментарии из службы поддержки:
"Человек, который закодировал представление, мог бы добавить операторы IF к вычислениям, чтобы дать Паприке знать, что делать, когда он сталкивается с делением на ноль, это остановило бы падение представления".

Код, который появляется с ошибкой:

(((COALESCE((SELECT SUM(JF_AMOUNT) 
             FROM MAV.UK.JOB_BUD_FORECAST, MAV.UK.NOMINAL_PERIOD 
             WHERE JF_JO_MN=JO_MN AND JF_FORECAST_TYPE=1 AND JF_NP_MN=NP_MN AND NP_PERIOD_KEY = '201812'),
            (SELECT SUM(JF_AMOUNT) 
             FROM MAV.USA.JOB_BUD_FORECAST, MAV.USA.NOMINAL_PERIOD
             WHERE JF_JO_MN=(JO_MN) AND JF_FORECAST_TYPE=1 AND JF_NP_MN=NP_MN AND NP_PERIOD_KEY = '201812'),
            (SELECT SUM(JF_AMOUNT)
             FROM MAV.BR.JOB_BUD_FORECAST, MAV.BR.NOMINAL_PERIOD 
             WHERE JF_JO_MN=(JO_MN) AND JF_FORECAST_TYPE=1 AND JF_NP_MN=NP_MN AND NP_PERIOD_KEY = '201812'),
            0))*JO_PROBABILITY/100)/CUR_RATE)*
    COALESCE(((SELECT SUM(JB_CHARGE) 
               FROM MAV.UK.JOB_BUDFORM 
               WHERE JB_JO_MN=JO_MN AND JB_GRADE IN('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR') AND JB_REV=JO_BUD_REVISION)/
              (SELECT SUM(JB_CHARGE) 
               FROM MAV.UK.JOB_BUDFORM 
               WHERE JB_JO_MN=JO_MN AND JB_GRADE NOT IN(' ') AND JB_REV=JO_BUD_REVISION)),
             ((SELECT SUM(JB_CHARGE) 
               FROM MAV.USA.JOB_BUDFORM 
               WHERE JB_JO_MN=(JO_MN) AND JB_GRADE IN('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR') AND JB_REV=JO_BUD_REVISION)/
              (SELECT SUM(JB_CHARGE) 
               FROM MAV.USA.JOB_BUDFORM
               WHERE JB_JO_MN=(JO_MN) AND JB_GRADE NOT IN(' ') AND JB_REV=JO_BUD_REVISION)),
             ((SELECT SUM(JB_CHARGE) 
               FROM MAV.BR.JOB_BUDFORM 
               WHERE JB_JO_MN=(JO_MN) AND JB_GRADE IN('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR') AND JB_REV=JO_BUD_REVISION)/
              (SELECT SUM(JB_CHARGE) FROM MAV.BR.JOB_BUDFORM WHERE JB_JO_MN=(JO_MN) AND JB_GRADE NOT IN(' ') AND JB_REV=JO_BUD_REVISION)),
             0)
Источник

2 ответа

Решение

Боже мой: в хорошем SQL нет такого выбора в select, мы должны использовать подзапрос и / или join.

плохой SQL

(select A from toto where A=T.AA), T.*
from mytable T

правильный SQL (ANSI 92)

select toto.A, T.*
from toto 
inner join T
on toto.A =T.AA

правильный SQL (старый SQL или сгенерированный ботом)

select toto.A, T.*
from toto,T
where toto.A =T.AA

Чтобы ответить на вопрос для A / B, если B может быть равен нулю без ошибок:

select
case 
  when coalesce(T.B,0)<> 0 
    then T.A/T.B 
end
from my_table as T

или же

select
case 
  when coalesce(T.B,0)<> 0 
    then T.A/T.B 
  else 0
end
from my_table as T
Источник

Самое первое, что нужно сделать с этим запросом, это отформатировать его, чтобы его могли прочитать простые люди, например, я взял этого монстра в онлайн-инструмент для форматирования T-SQL и добавил слово select затем вставьте и нажмите кнопку форматирования, чтобы показать это:

SELECT
    (((COALESCE( (
        SELECT
            SUM( JF_AMOUNT )
        FROM MAV.UK.JOB_BUD_FORECAST
           , MAV.UK.NOMINAL_PERIOD
        WHERE JF_JO_MN = JO_MN
        AND JF_FORECAST_TYPE = 1
        AND JF_NP_MN = NP_MN
        AND NP_PERIOD_KEY = '201812'
    )
    , (
        SELECT
            SUM( JF_AMOUNT )
        FROM MAV.USA.JOB_BUD_FORECAST
           , MAV.USA.NOMINAL_PERIOD
        WHERE JF_JO_MN = (JO_MN)
        AND JF_FORECAST_TYPE = 1
        AND JF_NP_MN = NP_MN
        AND NP_PERIOD_KEY = '201812'
    )
    , (
        SELECT
            SUM( JF_AMOUNT )
        FROM MAV.BR.JOB_BUD_FORECAST
           , MAV.BR.NOMINAL_PERIOD
        WHERE JF_JO_MN = (JO_MN)
        AND JF_FORECAST_TYPE = 1
        AND JF_NP_MN = NP_MN
        AND NP_PERIOD_KEY = '201812'
    )
    , 0 ))
    * JO_PROBABILITY / 100)
    / CUR_RATE
    ) * COALESCE( ((
        SELECT
            SUM( JB_CHARGE )
        FROM MAV.UK.JOB_BUDFORM
        WHERE JB_JO_MN = JO_MN
        AND JB_GRADE IN ('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR')
        AND JB_REV = JO_BUD_REVISION
    )
    / (
        SELECT
            SUM( JB_CHARGE )
        FROM MAV.UK.JOB_BUDFORM
        WHERE JB_JO_MN = JO_MN
        AND JB_GRADE NOT IN (' ')
        AND JB_REV = JO_BUD_REVISION
    )
    ), ((
        SELECT
            SUM( JB_CHARGE )
        FROM MAV.USA.JOB_BUDFORM
        WHERE JB_JO_MN = (JO_MN)
        AND JB_GRADE IN ('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR')
        AND JB_REV = JO_BUD_REVISION
    )
    / (
        SELECT
            SUM( JB_CHARGE )
        FROM MAV.USA.JOB_BUDFORM
        WHERE JB_JO_MN = (JO_MN)
        AND JB_GRADE NOT IN (' ')
        AND JB_REV = JO_BUD_REVISION
    )
    ), ((
        SELECT
            SUM( JB_CHARGE )
        FROM MAV.BR.JOB_BUDFORM
        WHERE JB_JO_MN = (JO_MN)
        AND JB_GRADE IN ('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR')
        AND JB_REV = JO_BUD_REVISION
    )
    / (
        SELECT
            SUM( JB_CHARGE )
        FROM MAV.BR.JOB_BUDFORM
        WHERE JB_JO_MN = (JO_MN)
        AND JB_GRADE NOT IN (' ')
        AND JB_REV = JO_BUD_REVISION
    )
    ), 0 )

Я думаю, что большая часть данных может быть получена в виде 2 соединенных подзапросов, например:

SELECT
  , jo.JO_MN           , jo.JO_BUD_REVISION
  , jo.JO_PROBABILITY  , jo.CUR_RATE
  , s1.SUM_JF_AMOUNT   , s2.SUM_JB_CHARGE_IN , s2.SUM_JB_CHARGE_NOT_IN
FROM unknown_table jo
LEFT JOIN (
        SELECT
            JF_JO_MN
          , SUM( JF_AMOUNT )                      AS SUM_JF_AMOUNT
        FROM MAV.UK.JOB_BUD_FORECAST
        INNER JOIN MAV.UK.NOMINAL_PERIOD ON JF_NP_MN = NP_MN
        WHERE JF_FORECAST_TYPE = 1
        AND NP_PERIOD_KEY = '201812'
        GROUP BY
            JF_JO_MN
    ) s1 ON JF_JO_MN = JO_MN
LEFT JOIN (
        SELECT
            JB_REV
          , SUM( CASE
                WHEN JB_GRADE IN ('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR') 
                THEN JB_CHARGE
            END )                                 AS SUM_JB_CHARGE_IN
          , SUM( CASE
                WHEN JB_GRADE NOT IN (' ') 
                THEN JB_CHARGE
            END )                                 AS SUM_JB_CHARGE_NOT_IN
        FROM MAV.UK.JOB_BUDFORM
        WHERE (
                JB_GRADE IN ('AUT', 'EDT', 'FMAN', 'FPRN', 'FDIR', 'SAUT', 'FDIR')
             OR JB_GRADE NOT IN (' ')
              )
        GROUP BY
            JB_REV
    ) s2 ON JB_REV = JO_BUD_REVISION

Если вы используете подобную структуру запросов, вы сможете легче получить доступ к требуемым номерам, и тогда проблема NULL также может быть легко решена.

ПРИМЕЧАНИЕ: вам нужно улучшить синтаксис соединения каждого подзапроса. Сделайте себе одолжение, прекратите использовать запятые между именами таблиц, это поможет вам принять "более новый" синтаксис (который был опубликован более 25 лет назад).

Источник
Другие вопросы по тегам