Solved

Divide by Zero Error in SQL

Posted on 2016-09-14
7
41 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 28

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 28

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

740 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