?
Solved

Division by zero - CASE does not help

Posted on 2004-08-31
9
Medium Priority
?
624 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 15

Accepted Solution

by:
jdlambert1 earned 1500 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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