Solved

Division by zero - CASE does not help

Posted on 2004-08-31
9
620 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
ID: 11943634
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
ID: 11943650
StdDev can not be null - it will always have a value which can sometimes be zero.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11943685
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 11943728
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
 
LVL 1

Author Comment

by:gpretorius
ID: 11943759
The underlying queries work fine.
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 11943813
try
CASE
          WHEN dbo.vw_MeanCustPriceStdDev.StdDev  != 0
0
 
LVL 2

Expert Comment

by:nexusSam
ID: 11944501
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
ID: 11945169
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
ID: 11945500
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

733 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