SQL 在视图中将数据类型 nvarchar 转换为数字时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14268866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error converting data type nvarchar to numeric in view
提问by ricardocasares
I have a view:
我有一个观点：
SELECT
u.display_name AS usuario,
g.parent_name AS grupo,
pr.pkey,
REPLACE(
CONVERT (VARCHAR, ji.CREATED, 111),
'/',
'-'
) AS fecha,
CAST (ji.issuetype AS INT) AS issuetype,
a.customvalue AS aplicativo,
m.customvalue AS modulo
FROM
jiraissue AS ji
JOIN project pr ON pr.ID = ji.PROJECT
JOIN (
SELECT
ms.*
FROM
cwd_membership ms
INNER JOIN cwd_group gp ON (
gp.ID = ms.parent_id
AND group_name IN (
'Grupo QA 1',
'Grupo QA 2',
'Grupo QA 3',
'BH Seguros Homo'
)
)
) g ON g.lower_child_name = ji.REPORTER
JOIN cwd_user u ON g.lower_child_name = u.user_name
JOIN (
SELECT
ISSUE,
customvalue
FROM
customfieldvalue v
INNER JOIN customfield f ON (
f.ID = v.customfield
AND f.cfname = 'Aplicativo'
)
INNER JOIN customfieldoption o ON (o.ID = v.STRINGVALUE)
) a ON (a.ISSUE = ji.ID)
JOIN (
SELECT
ISSUE,
customvalue
FROM
customfieldvalue v
INNER JOIN customfield f ON (
f.ID = v.customfield
AND f.cfname = 'Módulo'
)
INNER JOIN customfieldoption o ON (o.ID = v.STRINGVALUE)
) m ON (m.ISSUE = ji.ID)
WHERE
ji.issuetype IN (9, 11, 12, 13, 14, 15)
GROUP BY
ji.issuetype,
pr.pkey,
g.parent_name,
u.display_name,
REPLACE(
CONVERT (VARCHAR, ji.CREATED, 111),
'/',
'-'
),
a.customvalue,
m.customvalue
And this gives me something like this:
这给了我这样的东西：
usuario grupo pkey fecha issuetype aplicativo
----------------------------------------------------------------------------------
Ricardo A. Casares Grupo QA 1 GD123 2012-11-23 12 Act-creditos-scheduler ABM_Suc-backend
And then, when I try to query this view, let's say a simple query:
然后，当我尝试查询此视图时，让我们说一个简单的查询：
SELECT * FROM view
WHERE pkey LIKE '%GD123%'
In some columns I'm getting "Error converting data type nvarchar to numeric" But in some other columns, like "aplicativo" it is working fine.
在某些列中，我收到“将数据类型 nvarchar 转换为数字时出错”但在其他一些列中，例如“aplicativo”，它运行良好。
Why is this happening?
为什么会这样？
回答by cha
The problem is with this assignment:
问题在于这个任务：
o.ID = v.STRINGVALUE
please correct it and it will solve the issue. A possible way to correct the issue is to use ISNUMERIC, e.g.
请更正它，它将解决问题。纠正问题的一种可能方法是使用 ISNUMERIC，例如
o.ID = CASE WHEN ISNUMERIC(v.STRINGVALUE) = 1 THEN v.STRINGVALUE ELSE -1 END
(in the ELSE you can use some other number that is guaranteed to not join with table 'o', i.e. you can use 0)
（在 ELSE 中，您可以使用其他一些保证不与表 'o' 连接的数字，即您可以使用 0）
回答by Blachshma
This error means that you have some column which is of type nvarcharand you're trying to make a conversion to a numeric value (int, for instance).
BUTin at least on of the rows returned, that value cannot be converter to a numeric.
此错误意味着您有一些类型的列，nvarchar并且您正在尝试转换为数值（例如 int）。
但至少在返回的行中，该值不能转换为数字。
For example - trying to convert "abcd" to a numeric will raise that error.
例如 - 尝试将“abcd”转换为数字会引发该错误。
In the query you supplied it is probably on the line: CAST (ji.issuetype AS INT) AS issuetype
在您提供的查询中，它可能在线： CAST (ji.issuetype AS INT) AS issuetype
Check if any of the data in ji.issuetypeis not a string represention of a number...
检查是否有任何数据ji.issuetype不是数字的字符串表示...
回答by HeavenCore
Another thing you can try is to use ARITHABORTto tell SQL to just NULL values it cant cast.
您可以尝试的另一件事是用来ARITHABORT告诉 SQL 只是它不能转换的 NULL 值。
For example, using SET ARITHABORT OFF& SET ANSI_WARNINGS OFFbefore selecting from the view should NULL the values it fails to cast (instead of throwing exceptions) - you can then see what rows (and in turn what values) are giving you grief.
例如，在从视图中选择之前使用SET ARITHABORT OFF&SET ANSI_WARNINGS OFF应该将它无法转换的值设为 NULL（而不是抛出异常） - 然后您可以看到哪些行（以及哪些值）让您感到悲伤。
