sqlnullcoalescedivide-by-zeronullif

Handle error due to division by zero using SQL


The database engine is a finance software called Paprika. It's purpose is to pull out revenue in December 2018 for a particular department.

(December 2018 project value across 3 databases (UK, US, BR)job prob/FX rate)(department hours based on grade/total hours across databases)

The zero values are coming from the last part (the last 3 divides) - in pulling out hours by grade for each project, many of them have zero hours.

Below are the comments from the helpdesk:
"The person who coded the view could have added IF statements to the calculation's to let Paprika know what to do when it encounters a divide by zero, this would have stopped the view falling over."

The code, which comes up with an error:

(((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)

Solution

  • My god : in good SQL there is not select in select like that, we have to use subquery and/or join.

    bad SQL

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

    correct SQL (ANSI 92)

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

    correct SQL (old SQL or generated by bot)

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

    To answer the question for A / B if B could be equal to zero without error:

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

    or

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