UNION 导致“将 varchar 值转换为 int 时转换失败"； [英] UNION causes "Conversion failed when converting the varchar value to int"

问题描述

我尝试搜索以前与此相关的文章，但找不到针对我的情况的文章.由于我是 StackOverflow 的新手，我无法发布图片，所以我会尝试描述它.

我有两个数据集.一个是所有 NULL 的 34 行，1 列.其他 13 行，1 列 varchars.

当我尝试将这两个 UNION ALL 结合在一起时，出现以下错误:

<块引用>

将 varchar 值转换为数据类型 int 时转换失败.

我不明白为什么我会收到这个错误.我之前UNION编辑过许多NULL 列和varchar 列，以及许多其他类型，但我没有收到此转换错误.

谁能提供建议为什么会发生此错误?

解决方案

出现错误是因为您在两个子查询中有对应的列，其中一个是整数类型，另一个是字符类型.然后，该字符值至少在一行中具有无法自动转换为整数的值.

这很容易复制:

选择t.*from (选择'A'作为col union all选择 1) t;

这里是对应的SQL Fiddle.

SQL Server 使用非常复杂的类型优先规则来确定 union 中的目标类型.但在实践中，最好避免使用隐式类型转换.相反，将列显式转换为您想要的类型.

NULL 值的情况很复杂.就其本身而言，NULL 值没有类型.因此，以下工作正常:

选择NULL作为col联合所有选择一个';

如果你输入NULL，那么查询就会失败:

select cast(NULL as int) as col联合所有选择一个';

此外，如果您将 SQL Server 置于必须为其分配类型的位置，则 SQL Server 会将 NULL 设为整数.表或结果集中的每一列都需要一个类型，所以这也会失败:

select (select NULL) as col联合所有选择一个';

也许您的查询正在做这样的事情.

I tried to search for previous articles related to this, but I can't find one specific to my situation. And because I'm brand new to StackOverflow, I can't post pictures so I'll try to describe it.

I have two datasets. One is 34 rows, 1 column of all NULLs. The other 13 rows, 1 column of varchars.

When I try to UNION ALL these two together, i get the following error:

Conversion failed when converting the varchar value to data type int.

I don't understand why I'm getting this error. I've UNIONed many NULL columns and varchar columns before, among many other types and I don't get this conversion error.

Can anyone offer suggestions why this error occurs?

解决方案

The error occurs because you have corresponding columns in the two of the subqueries where the type of one is an integer and the type of the other is a character. Then, the character value has -- in at least one row -- a value that cannot be automatically converted to an integer.

This is easy to replicate:

select t.*
from (select 'A' as col union all
      select 1
     ) t;

Here is the corresponding SQL Fiddle.

SQL Server uses pretty sophisticated type precedence rules for determining the destination type in a union. In practice, though, it is best to avoid using implicit type conversions. Instead, explicitly cast the columns to the type you intend.

EDIT:

The situation with NULL values is complicated. By itself, the NULL value has no type. So, the following works fine:

select NULL as col
union all
select 'A';

If you type the NULL, then the query will fail:

select cast(NULL as int) as col
union all
select 'A';

Also, if you put SQL Server in a position where it has to assign a type, then SQL Server will make the NULL an integer. Every column in a table or result set needs a type, so this will also fail:

select (select NULL) as col
union all
select 'A';

Perhaps your queries are doing something like this.

这篇关于UNION 导致“将 varchar 值转换为 int 时转换失败"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！