Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Divide by Zero Error in SQL

Posted on 2016-09-14
7
Medium Priority
?
67 Views
Last Modified: 2016-09-14
I have the following code snippet that is throwing a divide by zero error. How do I resolve this and more importantly can I identify the record or records that is causing the issue?

        ( ( CASE WHEN D.line_status = 'I'
                 THEN ( D.price * D.total_qty
                        - ROUND({FN IFNULL(R.disc_perc, 0)} * D.price
                                * D.total_qty / 100, 4) )
                 ELSE 0
            END ) * D.co_rate ) AS SFC_TOTAL_INV_EXT_B ,
        ( (CASE WHEN D.line_status = 'I' THEN D.total_qty
                ELSE 0
           END) ) AS SFS_TOTAL_INV ,

Open in new window

0
Comment
Question by:mburk1968
  • 3
  • 2
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41797839
the only division in that snippet is dividing by 100


I don't think that snippet is where your error is happening.
0
 

Author Comment

by:mburk1968
ID: 41797842
Wrong snippet
        --( (( CASE WHEN D.line_status = 'I'
        --          THEN ( D.price * D.total_qty
        --                 - ROUND({FN IFNULL(R.disc_perc, 0)} * D.price
        --                         * D.total_qty / 100, 4) )
        --          ELSE 0
        --     END ) / ( CASE WHEN D.line_status = 'I' THEN D.total_qty
        --                    ELSE 0
        --               END )) ) AS UDF_ZZOODRPB_098 ,

Open in new window

0
 
LVL 29

Expert Comment

by:Michael Pfister
ID: 41797849
It will happen when D.line_status is not equal to 'I' or when D.total_qty is zero.
In each case
( CASE WHEN D.line_status = 'I' THEN D.total_qty
        --                    ELSE 0
        --               END )

will return 0 to the divisor, which is not a valid operation.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 41797850
try returning NULL instead of 0 in your ELSE.

Then your division will return NULL, and you can then search for rows that have null results.

it's also possible you have rows with D.total_qty = 0.
0
 
LVL 29

Assisted Solution

by:Michael Pfister
Michael Pfister earned 1000 total points
ID: 41797854
So you could do a select for D.total_qty = '0' and another select for D.line_status != 'I' to identify the problematic records.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41797869
d.line_status is null    would also drop you into your ELSE clause
0
 

Author Closing Comment

by:mburk1968
ID: 41797870
Duh! Thank you both. Don't know where my head was at this morning.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

824 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