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)

Hi Anandkin, sorry but looks so dificult to understand your query, could you please put an image of the database diagram? It would be more helpfull, and explain of the expeted results. These types of queries break all good practices and may become unmanageable, someone need to put all in order. What I would do is to do everything from scratch — Cristina Carrasco, Nov 30 '18 at 23:19
There is no simple solution because the whole query is a disaster, In a clean code, you could fix it simple with an isnull or a case. — Cristina Carrasco, Nov 30 '18 at 23:22
For any future questions, please include the complete query, not just a part of it. As you will see in my answer below I had to guess part of my response. I also urge you to use formatting of your SQL, your coding life will be much more productive if your SQL is easily read. — Paul Maxwell, Dec 01 '18 at 01:05

phili_b · Accepted Answer · 2018-11-30T23:26:37.387

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

Paul Maxwell · Answer 2 · 2018-12-01T01:00:14.760

The very first thing to do with that query is to FORMAT IT so that it can be read by mere humans e.g. I took that monster to an online T-SQL formatting tool added the word select then pasted then and hit the format button to reveal this:

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 )

I think the majority of the data could be sourced as 2 joined subqueries, like this:

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

If you use a query structure like this you can access the wanted numbers more easily, and the problem of NULLs can then be solved easily too.

NOTE you need to improve the join syntax of each subquery. Do yourself a favour, stop using commas between table names, this will help you adopt the "newer" syntax (which was published over 25 years ago).

Hi All Just woke up after my making my first post a few hours ago...amazed to have 2 answers already. Thanks for your time Phil, Cristina and Used_By_Already!!! I did not write this code, an ex employee did and I couldn't even write that as i'm a beginner (SQL isn't required for my job, but beneficial) and it has been hard to learn off his code! I did get it working last night but there aren't enough characters to show any of you here (if you even wanted to see). I also appreciate the advice to make any future posts I have (if any) more efficient for you guys to help out. — Anandkin Skywalker, Dec 01 '18 at 08:21

Handle error due to division by zero using SQL

2 Answers2