Divide by Zero Error in SQL

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

mburk1968Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberCommented:
the only division in that snippet is dividing by 100


I don't think that snippet is where your error is happening.
0
 
mburk1968Author Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Michael PfisterCommented:
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
 
Michael PfisterConnect With a Mentor Commented:
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
 
sdstuberCommented:
d.line_status is null    would also drop you into your ELSE clause
0
 
mburk1968Author Commented:
Duh! Thank you both. Don't know where my head was at this morning.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.