Divide by zero error.

  • 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,

  • 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

  • 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]

  • 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

  • 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'

  • 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.

     

     

  • 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

     

  • 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,

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

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