Solved

Division by zero - CASE does not help

Posted on 2004-08-31
9
618 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 97
SQL Improvement  ( Speed) 14 28
sql server service accounts 4 27
abs operation in sql server 15 15
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

803 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