CODEWITHSUNDEEP
sql
S.Carter
S.Carter

Reputation: 21

SQL divide by zero error, nullif not helping

I am trying to work out this statement but can't get anywhere.

From what I have found so far, I think I need to use an nullif clause for each divisor, but when I try and do this it still does not work.

Here is the statement with no nullif's that produces a divide by zero error.

(1 - (1 - (x1.hup / (x1.hup / (x1.dp / 100)))) / (1 - (x2.sdp / 100)))

Upvotes: 1

Views: 254

Answers (4)

Pedro Fialho
Pedro Fialho

Reputation: 441

Coalescing to zero isn't the magic bullet you're looking for.

Just try to the simplify your problem; if you have 42 / x and x is null, replacing it with zero will just result in a division error.

I don't know about the formula you are applying in this case, but the main thing you are lacking is validation. In my example above, if x equals to 0 we've got a problem.

Similarly, in 23 / (100 - x), x can't ever be 100, so you must check that beforehand and handle the situation accordingly. No expression can result in 0 if it`s a divisor.

So, try to establish which constraints should be watched before processing; for instance, your statement must not accept 0 for x1.dp or x1.hup, and x2.sdp can be anything but 100 (1 - 100/100 = 0, right?). Should one of these situations happen, you could return an error or something.

Upvotes: 0

Matt
Matt

Reputation: 15071

Not very elegant but will do the job.

SELECT CASE WHEN (1 - (x2.sdp / 100) = 0 THEN NULL
            WHEN (x1.dp / 100) = 0 THEN NULL
            WHEN (x1.hup / (x1.dp / 100)) = 0 THEN NULL
            WHEN (x1.hup / (x1.hup / (x1.dp / 100))) = 0 THEN NULL
            ELSE (1 - (1 - (x1.hup / (x1.hup / (x1.dp / 100)))) / (1 - (x2.sdp / 100)))
            END AS field
FROM yourtable

Upvotes: 0

J Santosh
J Santosh

Reputation: 3847

This is Not the Answer

Create a function to check zero like this 

CREATE FUNCTION [dbo].[IsZero] (
    @Number FLOAT,
)
RETURNS FLOAT
AS
BEGIN

    IF (@Number = 0)
    BEGIN
        SET @Number = 1
    END

RETURN (@Number)

END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

There may be simpler ways to express this. I think this is all you need:

(1 - (1 - (x1.hup / (x1.hup / nullif(x1.dp / 100, 0)))) / nullif(1 - (x2.sdp / 100)), 0))

Upvotes: 2

Related Questions