[SOLVED] Divide by zero error in Sql View

Divide by zero error in Sql View

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,

Solution

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,