Divide by zero error.

Question

Post reply

Divide by zero error.

TriggerMe

SSChampion

Points: 11752
More actions
December 20, 2004 at 7:11 pm

#59877

How do I get the query below to work? I thought the OR's would have sorted it out!
SELECT CASE WHEN TOT_CALLS_INB_PDN = 0 OR TOTAL_LOGIN_TIME = 0 OR TOT_WAIT_TIME = 0 THEN 0
ELSE TOT_CALLS_INB_PDN / ((TOTAL_LOGIN_TIME - TOT_WAIT_TIME) / 3600) END AS 'TCPH'
Kindest Regards,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

You will get the error when TOTAL_LOGIN_TIME is equal to TOT_WAIT_TIME

Kathi

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

In which case you might use a nested case to test for this...

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Shane Osborne Valued Member Points: 66 More actions · Answer 3

Instead of nesting it why dont you just add the check to the case.

For example

SELECT CASE WHEN TOT_CALLS_INB_PDN = 0 OR TOTAL_LOGIN_TIME = 0 OR TOT_WAIT_TIME = 0 THEN 0 WHEN TOTAL_LOGIN_TIME = TOT_WAIT_TIME THEN 0

ELSE TOT_CALLS_INB_PDN / ((TOTAL_LOGIN_TIME - TOT_WAIT_TIME) / 3600) END AS 'TCPH'

This should now work

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 4

Surely, the only test that you need is for the equality (unless you really want 0 when TOT_WAIT_TIME is zero; you are, not unreasonably, assuming that TOT_WAIT_TIME never will exceed TOTAL_LOGIN_TIME) :

SELECT CASE WHEN TOTAL_LOGIN_TIME = TOT_WAIT_TIME THEN 0

ELSE 3600 * TOT_CALLS_INB_PDN / (TOTAL_LOGIN_TIME - TOT_WAIT_TIME) END AS 'TCPH'

PhilPacha Hall of Fame Points: 3645 More actions · Answer 5

Or, simply replace the offending zero in the divisor with NULL:

NULLIF((TOTAL_LOGIN_TIME - TOT_WAIT_TIME), 0)

This will cause the entire calculation to return NULL.

Then, if necessary, deal with the resulting NULL afterwards.

LynnDrew SSC Journeyman Points: 80 More actions · Answer 6

You can also use the set function to have the query continue even though it encounters the divide by zero error ... just be sure to turn it off after your SQL finishes.

SET ArithAbort OFF

SET ArithIgnore ON

GO

SET ArithAbort ON

SET ArithIgnore OFF

GO

TriggerMe SSChampion Points: 11752 More actions · Answer 7

Thanks to all for your responses. They are all valid responses and they all work as far as I'm concerned. However, I have been doing some playing around and I got around it by checking the subtraction for a zero and that has fixed it. Below is the query that now works. You may have to copy and paste it into your Query Analyzer window for clarity!

SELECT CASE WHEN TOT_CALLS_INB_PDN = 0 OR TOTAL_LOGIN_TIME = 0 OR TOT_WAIT_TIME = 0

OR (TOTAL_LOGIN_TIME - TOT_WAIT_TIME) / 3600 = 0 THEN 0

ELSE CAST(TOT_CALLS_INB_PDN AS DECIMAL(18,2)) / ((TOTAL_LOGIN_TIME - TOT_WAIT_TIME) / 3600) END

Kindest Regards,