将varchar转换为数据类型numeric的算术溢出错误。 '10'

Below is a subset of the kind of table structure and data i'm working with.

下面是我正在使用的表结构和数据类型的子集。

CREATE TABLE #Test
(
     Val varchar(5)
    ,Type varchar(5)
)

INSERT #Test VALUES ('Yes','Text')
INSERT #Test VALUES ('10','Int')
INSERT #Test VALUES ('10.00','Float')
INSERT #Test VALUES ('9.00','Float')
INSERT #Test VALUES ('9','Int')

I want to write a query that will let me know if the column 'Val' is <= 9.00 (must be of numeric data type). I did this by doing the following:

我想写一个查询,让我知道列'Val'是否<= 9.00(必须是数字数据类型)。我这样做是通过执行以下操作:

SELECT *
FROM
    (
        SELECT Val
        FROM #Test
        WHERE Type = 'Int'
    ) IntsOnly
WHERE IntsOnly.Val <= 9.00

This gives me an arithmetic overflow error. However, if I exclude the row of data with the value '10':

这给了我一个算术溢出错误。但是,如果我使用值“10”排除数据行:

SELECT *
FROM
    (
        SELECT Val
        FROM #Test
        WHERE Type = 'Int'
        AND Val <> '10'
    ) IntsOnly
WHERE IntsOnly.Val <= 9.00

It works without any issue. My question is not how to fix this as I know I can simply convert the data to the format I require.

它没有任何问题。我的问题不是如何解决这个问题,因为我知道我可以简单地将数据转换为我需要的格式。

My question is why the value of '10' in the column 'Val' is returning an error. Surely the logic should just return 'False' and simply exclude the rows because '10' (which I assume is implicitly converted) is greater than 9.00.

我的问题是为什么'Val'列中'10'的值返回错误。当然逻辑应该只返回'False'并简单地排除行,因为'10'(我假设是隐式转换的)大于9.00。

Thanks.

2 个解决方案

#1

This generates an Arithmetic Overflow because it is trying to implicitly cast the Val column to a NUMERIC(3,2), which naturally will overflow on a 2-digit value like 10.

这会生成算术溢出,因为它试图隐式地将Val列转换为NUMERIC(3,2),这自然会溢出2位数值,如10。

It's using NUMERIC(3,2) as the target type and size because that is the smallest numeric that 9.00 appears to fit into.

它使用NUMERIC(3,2)作为目标类型和大小,因为这是9.00似乎适合的最小数字。

The solution, of course, is to use explict CASTing instead of doing it implicitly

当然,解决方案是使用explict CASTing而不是隐式执行

#2

From BOL:

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

在Transact-SQL语句中,具有小数点的常量将使用所需的最小精度和小数位自动转换为数字数据值。例如,常量12.345将转换为精度为5且比例为3的数值。

That means your constant 9.00 will have ~~a precision of 1 and a scale of 0~~ a precision of 3 and a scale of 2, so it cannot store the value 10, which needs a minimum precision of 2 + scale.

这意味着你的常量9.00的精度为1,标度为0,精度为3,标度为2,因此无法存储值10,这需要最小精度为2 +标度。

You'll need to wrap the IntsOnly.Val with either a CAST or CONVERT to specify the correct precision and scale.

您需要使用CAST或CONVERT包装IntsOnly.Val以指定正确的精度和比例。

#1