Handle error due to division by zero using SQL

Question

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)

Answer 1

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