December 20, 2004 at 7:11 pm
December 20, 2004 at 10:22 pm
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
December 21, 2004 at 1:23 am
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]
December 22, 2004 at 2:21 am
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
December 22, 2004 at 2:30 am
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'
December 22, 2004 at 8:39 am
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.
December 22, 2004 at 1:59 pm
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
<your SQL code here>
SET ArithAbort ON
SET ArithIgnore OFF
GO
December 22, 2004 at 6:48 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply