错误将数据类型nvarchar转换成数字的视图。

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?

为什么会这样?

3 个解决方案

#1

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)

(在其他情况下，您可以使用其他一些保证不加入表'o'的数字，即您可以使用0)

#2

This error means that you have some column which is of type nvarchar and you're trying to make a conversion to a numeric value (int, for instance).
BUT in 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)。作为问题类型的问题类型。

Check if any of the data in ji.issuetype is not a string represention of a number...

检查ji是否有任何数据。问题类型不是一个数字的字符串。

#3

Another thing you can try is to use ARITHABORT to tell SQL to just NULL values it cant cast.

另一件你可以尝试的事情是使用算术方法将SQL告诉给它不能转换的空值。

For example, using SET ARITHABORT OFF & SET ANSI_WARNINGS OFF before 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算法关闭和设置ANSI_WARNINGS，这应该是NULL值，而不是抛出异常(而不是抛出异常)——您可以看到哪些行(以及哪些值)会给您带来痛苦。

#1