Generic workaround for Round Exception

  • Hi,

    I need a generic solution for this type of exception when using ROUND.

    SELECT ROUND(9.9996,3)

    this expression result in this exception : "Arithmetic overflow error converting expression to data type numeric"

    a known issue related to the fact that 9.9996 is actually typed as a DECIMAL(5,4) and when performing the round operation the result is no longer supported in this type. the result should be a 10.0000 a DECIMAL(6,4).

    A simple resolution for this specific case is to cast the value to a DECIMAL(6,4) :

    SELECT ROUND(CAST(9.9996 AS DECIMAL(6,4)),3)

    that's fine. it returns the expected results. but what if I don't know of the actual precision of the value beforehand.

    In my case, I'm performing some computing operation and I noticed that the results vary from an execution to another.

    the problem was that when performing some additions and divisions operations the result would slightly differ (E-10 or -11 difference). but this infinite difference result in more explicit one in the final result.

    the solution I came with is using round to ensure I always have the same results. And that's why I m looking for a generic workaround the Round issue.

    thx

    MBS

  • What's your calling application? Why are you not explicitly defining your data types before executing? The solution is to explicitly set your data type to the maximum precision and scale that can be an output.

  • Hi,

    I'm using float, and performing some computing operation with T-sql using TEMP tables.

    Syntax similar to this one :

    SELECTALIMENTER_ID,SUM(BILAN)

    INTO #TOTAUX

    FROMECRITURES

    GROUP BY ALIMENTER_ID

    BILAN is a float column.

    I don't know maybe the choice of the data type isn't appropriate. but I'm looking for an alternative to ROUND that just doesn't throw an exception in similar scenario.

    If a script is needed to describe this context, just let me know.

    hope it's a bit clear.

    thx

  • Still not understanding the problem. Rounding a float/real won't end up in this overflow unless you've reached the ceiling of the data type.

    At which point are you implicitly converting it to the minimum decimal type it could fit into? Yes, please can you post an example of the actual code that's causing the problem?

  • um... why not force the insert script into a known data type? I'm assuming you are using the insert script to create the table #TOTAUX...

    Something like:

    SELECT ALIMENTER_ID,CAST(SUM(BILAN) AS FLOAT)

    INTO #TOTAUX

    FROM ECRITURES

    GROUP BY ALIMENTER_ID

  • venoym (4/18/2011)


    um... why not force the insert script into a known data type? I'm assuming you are using the insert script to create the table #TOTAUX...

    Something like:

    SELECT ALIMENTER_ID,CAST(SUM(BILAN) AS FLOAT)

    INTO #TOTAUX

    FROM ECRITURES

    GROUP BY ALIMENTER_ID

    Actually you need to cast within the sum, not after since you'll get the error before you ever get to the cast!! :

    SELECT ALIMENTER_ID,SUM(CAST(BILAN AS FLOAT))

    INTO #TOTAUX

    FROM ECRITURES

    GROUP BY ALIMENTER_ID

Viewing 6 posts - 1 through 5 (of 5 total)

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