消息 8114，级别 16，状态 5，第 1 行将数据类型 varchar 转换为数字时出错 [英] Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric

问题描述

Select 
    CAST(de.ornum AS numeric) + 1 as ornum2 
from Cpaym as de
left outer join Cpaym as de1 on CAST(de.ornum AS numeric) = de1.ornum
where ISNUMERIC(de.ornum) = 1 

我正在尝试获取丢失的序列号，但出现错误:

I'm trying to get the missing sequence number, but I am getting an error:

消息 8114，级别 16，状态 5，第 1 行将数据类型 varchar 转换为数字时出错.

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

推荐答案

你的连接谓词中有一个 CAST()，它在 WHERE 子句之前被评估.如果 de.ornum 不是数字，则此转换将失败.

You have a CAST() in your join predicate, which is evaluated BEFORE the WHERE clause. If de.ornum is not numeric, then this cast will fail.

此外，IsNumeric() 不能捕获所有数字类型，但在大多数情况下它可能已经足够了.这是文档:http://technet.microsoft.com/en-us/library/ms186272(v=sql.105).aspx，这里有一篇(多篇)文章描述了 IsNumeric() 的问题:http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html.

Also, IsNumeric() doesn't catch all numeric types, but it's perhaps good enough in most cases. Here's the documentation: http://technet.microsoft.com/en-us/library/ms186272(v=sql.105).aspx and here is one (of many) articles describing the problems with IsNumeric(): http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html.

您可以做一些事情:

• 修复您的架构，以便名为 ornum 的列实际上在所有具有它的表中包含一个数字.
• 在您的连接谓词中使用 CASE 表达式(慢，但它会起作用):... ON CASE WHEN ISNUMERIC(de.ornum) = 1 THEN CAST(de.ornum AS numeric) ELSE NULL END = de1.ornum
• 在连接之前使用派生表进行预转换(也很慢，但可能不那么慢——检查执行计划)

• Fix your schema so that a column named ornum actually contains a number in all tables that have it.
• Use a CASE expression in your join predicate (slow, but it'll work): ... ON CASE WHEN ISNUMERIC(de.ornum) = 1 THEN CAST(de.ornum AS numeric) ELSE NULL END = de1.ornum
• Use a derived table to pre-convert before the join (also slow, but perhaps less slow -- check the execution plan)

代码:

FROM (
  SELECT de.ornum
    FROM Cpaym AS de
   WHERE IsNumeric(de.ornum) = 1
) AS de
LEFT OUTER JOIN ...

这篇关于消息 8114，级别 16，状态 5，第 1 行将数据类型 varchar 转换为数字时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！