Solved

Divide by Zero Error in SQL

Posted on 2016-09-14
7
55 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
[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
  • 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
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 74

Accepted Solution

by:
sdstuber earned 250 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 250 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

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