sql - 在 View 中将数据类型 nvarchar 转换为数字时出错

Question

我有一个观点:

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

这给了我这样的东西:

usuario             grupo      pkey     fecha       issuetype  aplicativo 
----------------------------------------------------------------------------------
Ricardo A. Casares  Grupo QA 1  GD123   2012-11-23  12  Act-creditos-scheduler  ABM_Suc-backend

然后，当我尝试查询此 View 时，让我们说一个简单的查询:

SELECT * FROM view
WHERE pkey LIKE '%GD123%'

在某些列中，我收到“将数据类型 nvarchar 转换为数字时出错”但在其他一些专栏中，例如“applicativo”，它运行良好。

为什么会发生这种情况？

Answer 1

问题出在这个作业上:

o.ID = v.STRINGVALUE

请纠正它，问题就会解决。解决此问题的一种可能方法是使用 ISNUMERIC，例如

o.ID = CASE WHEN ISNUMERIC(v.STRINGVALUE) = 1 THEN v.STRINGVALUE ELSE -1 END

(在 ELSE 中，您可以使用保证不会与表“o”连接的其他数字，即您可以使用 0)