Solved

Division by zero - CASE does not help

Posted on 2004-08-31
9
616 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:gpretorius
9 Comments
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
Could it be NULL values tripping you up?

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

AustinSeven
0
 
LVL 1

Author Comment

by:gpretorius
Comment Utility
StdDev can not be null - it will always have a value which can sometimes be zero.
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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 ...
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
Comment Utility
It looks to me like your WHEN condition will prevent divide by zero in your coded calculation, so I suspect a problem with the combination of your data, the group by, and the AVG. Try taking out the AVG function, leaving the POWER function, and see if that eliminates the error.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:gpretorius
Comment Utility
The underlying queries work fine.
0
 
LVL 18

Expert Comment

by:bobbit31
Comment Utility
try
CASE
          WHEN dbo.vw_MeanCustPriceStdDev.StdDev  != 0
0
 
LVL 2

Expert Comment

by:nexusSam
Comment Utility
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
0
 
LVL 7

Expert Comment

by:wesbird
Comment Utility
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.

0
 
LVL 1

Author Comment

by:gpretorius
Comment Utility
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now