asked on

Division by zero - CASE does not help

I have the following query which currently gives a Division by Zero error. Any idea as to why the CASE statement does not catch it?

SELECT dbo.vw_OrderHistory_1m.ProductID,
            CASE
            WHEN dbo.vw_MeanCustPriceStdDev.StdDev <> 0
            THEN
            AVG(POWER((dbo.vw_OrderHistory_1m.Price - dbo.vw_MeanCustPriceStdDev.Mean)/ dbo.vw_MeanCustPriceStdDev.StdDev, 3))
            ELSE 0
            END
            AS Skew,
            CASE
            WHEN dbo.vw_MeanCustPriceStdDev.StdDev <> 0
            THEN
            AVG(POWER((dbo.vw_OrderHistory_1m.Price - dbo.vw_MeanCustPriceStdDev.Mean)/ dbo.vw_MeanCustPriceStdDev.StdDev, 4) - 3)
            ELSE 0
            END
            AS Kurt,
            dbo.vw_MeanCustPriceStdDev.Mean,
            dbo.vw_MeanCustPriceStdDev.StdDev
FROM dbo.vw_OrderHistory_1m INNER JOIN
dbo.vw_MeanCustPriceStdDev ON dbo.vw_OrderHistory_1m.ProductID = dbo.vw_MeanCustPriceStdDev.ProductID
GROUP BY dbo.vw_OrderHistory_1m.ProductID, dbo.vw_MeanCustPriceStdDev.Mean, dbo.vw_MeanCustPriceStdDev.StdDev

AustinSeven

Could it be NULL values tripping you up?

WHEN dbo.vw_MeanCustPriceStdDev.StdDev <> 0 and dbo.vw_MeanCustPriceStdDev.StdDev is NOT NULL

AustinSeven

gpretorius

ASKER

StdDev can not be null - it will always have a value which can sometimes be zero.

Hilaire

Did you check the error doen't come from the underlying views ( dbo.vw_OrderHistory_1m and dbo.vw_MeanCustPriceStdDev ) ?

Try a select * on both views ...

ASKER CERTIFIED SOLUTION

jdlambert1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gpretorius

ASKER

The underlying queries work fine.

bobbit31

try
CASE
WHEN dbo.vw_MeanCustPriceStdDev.StdDev != 0

nexusSam

Do you know which of the three operations is causing a divide by error? If it's not both then your case is not to blame
Otherwise try reversing the CASE;
CASE
WHEN dbo.vw_MeanCustPriceStdDev.StdDev =0
THEN 0 ELSE
AVG(POWER((dbo.vw_OrderHistory_1m.Price - dbo.vw_MeanCustPriceStdDev.Mean)/ dbo.vw_MeanCustPriceStdDev.StdDev, 3))
END
AS Skew,

Incidentally, AustinSteven; dividing by null doesn't give an error but evaluates the whole expression to null
I

wesbird

Note for gpretorius.

STDEV can return NULL if there is only one row of data in the population (or at least it does on MSSQL7). According to BOL, StdDev is an alias for STDEV.

I think you should replace:

WHEN dbo.vw_MeanCustPriceStdDev.StdDev <> 0

with

WHEN ISNULL(dbo.vw_MeanCustPriceStdDev.StdDev , 0 ) <> 0

to replace nulls with zeros so that they don't cancel any comparisons out.

gpretorius

ASKER

Note to wesbird:

I am using StDevP which gives you the population StDev and does not evaluate to null when the population is one only. I have verified this against data - but thanks for the tip.

I have resolved the issue by moving the AVG to outside the CASE statement.