UNION ALL SQL ERROR将数据类型varchar转换为numeric [英] UNION ALL SQL ERROR Converting data type varchar to numeric

问题描述

美好的一天！



我想就我的问题寻求帮助。



这是我的询问：

Hi, Good Day!

I would like to ask some help regarding my problem.

This is my query:

SELECT name [Name],
       allowance [Allowance]
FROM Table1

UNION ALL

SELECT name [Name],
       '' [Allowance] <--
FROM Table2

表2中没有允许字段。我希望它有一个BLANK数据。

但是发生错误，

There's no allowance field in the Table2. I want it to have a BLANK data.
But an error occurred,

"Error converting data type varchar to numeric."

我知道这是因为，allowance字段在Table1中有一个数值数据类型，我将它与''联合起来。我该怎么办？我希望它有一个空白数据或空格。不是0，或者是NULL。



我不想用

I know that it is because, allowance field in Table1 has a numeric datatype and I union it with a ''. How will I do it? I want it to have a Blank Data or Space. NOT 0, or NULL.

I don't want to use

SELECT name [Name],
       allowance [Allowance]
FROM Table1

UNION ALL

SELECT name [Name],
       0 [Allowance] <-- 0
FROM Table2

< br $>


OR NULL值

OR NULL Value

SELECT name [Name],
       allowance [Allowance]
FROM Table1

UNION ALL

SELECT name [Name],
       NULL [Allowance] <-- NULL
FROM Table2

请帮助。

非常感谢你。

Please Help.
Thank you so much.

推荐答案

你好，



你需要将Table1中Allowance中的字段转换为varchar类型。

Hi,

You need to convert the field in Allowance in Table1 to varchar type.

SELECT name [Name],
       Convert(varchar(100),allowance) as [Allowance]
FROM Table1
UNION ALL
SELECT name [Name],
      '' as Allowance
FROM Table2

因为allowance字段现在是varchar ，将它与没有varchar allowance的表连接，实际上会显示一个空格。

Since allowance field is now a varchar, joining it with a table that has no varchar allowance will actually show a space.

你可以尝试一下通过在select语句中转换数据类型..

Hi,

you can try it by converting data type in your select statement..

create table table1
(
name varchar(10),
allowance int
)


create table table2
(
name varchar(10),
)

insert into table1 values('amit',1000),('arun',2000),('nitin',1000)
insert into table1 values('ajay'),('kamal'),('kunal')

SELECT Name,Convert(varchar,allowance) as Allowance
FROM Table1
UNION ALL
SELECT Name ,'' as Allowance
FROM Table2

输出：

Output:

Name	Allowance
amit	1000
arun	2000
nitin	1000
ajay	
kamal	
kunal	

如果是上面的表结构..你的查询也没有给出任何错误..

And in case of above table structure.. your query is also not giving any error..

SELECT Name [Name],
       allowance [Allowance]
FROM Table1

UNION ALL

SELECT name [Name],
       '' [Allowance]
FROM Table2

OutPut：

OutPut:

Name	Allowance
amit	1000
arun	2000
nitin	1000
ajay	0
kamal	0
kunal	0

这篇关于UNION ALL SQL ERROR将数据类型varchar转换为numeric的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！