Divide by zero error in Sql View

Question

Every time implement this part of my select statement I get an Divide by zero exception. I tried replacing ISNULL with NUllIF. Same error. here is my code:

isnull([Balance], 0) * isnull(sce.tradepoundsperunit, 0)  * (isnull(limitallocation_limitcommodity.priceperpound, 0) / CASE WHEN ISNULL(limit_limitcommodity.priceperpound, 1) = 0 THEN 1 ELSE  ISNULL(limit_limitcommodity.priceperpound, 1) END )  / isnull(CASE WHEN ISNULL(l.PoundsPerUnit, 1) = 0 THEN 1 ELSE  ISNULL(l.PoundsPerUnit, 1) END *  ISNULL(targetu.bushelsperunit, 1) ,1)   

AS Limitconvertedbalance,

Answer 1

I think any of the clauses: ISNULL(limit_limitcommodity.priceperpound, 1) ISNULL(l.PoundsPerUnit, 1) ISNULL(targetu.bushelsperunit, 1)

could be returning 0 because you are only checking for null and not zero. e.g. if l.PoundsPerUnit=0 then checking ISNULL(l.PoundsPerUnit,1) is still going to return zero.

I think that something like this should solve your problem. Instead of checking for null it checks for both null or zero for all denominators and inserts 1 instead.

isnull([Balance],0) *
isnull(sce.tradepoundsperunit,0) *
(
    isnull(limitallocation_limitcommodity.priceperpound,0)
    /
    CASE
        WHEN limit_limitcommodity.priceperpound IS NULL OR limit_limitcommodity.priceperpound=0 THEN 1
        ELSE limit_limitcommodity.priceperpoind
    END
) /
(
    CASE 
        WHEN l.PoundsPerUnit IS NULL OR l.PoundsPerUnit =0 THEN 1
        ELSE l.PoundsPerUnit
    END *
    CASE
        WHEN targetu.bushelsperunit IS NULL OR targetu.bushelsperunit=0 THEN 1
        ELSE targetu.bushelsperunit
    END
)
AS Limitconvertedbalance,

Answer 2

I believe the answer may be in this bit:

* ISNULL(targetu.bushelsperunit, 1)

If targetu.bushelsperunit is 0, the code could be executing that * before it executes the previous /, resulting in a divide by 0. Is that field meant to be in the denominator?

Answer 3

Try with the "nvl" function. Like nvl(yourfield, 0)

Answer 4

You should check the statement after the / because possibly the CASE WHEN statement may result in ZERO. Use another CASE WHEN to be sure there are no Zero after the / Also have an eye on * because it may ZERO the result, where you don't expect it!