Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Division by zero - CASE does not help

Posted on 2004-08-31
9
Medium Priority
?
626 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

636 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