问题描述
首先祝大家新年快乐.我在编写查询时遇到问题.执行查询时出现错误.
First of all Wish u all Happy New Year. I have a problem in writing query. While executing my query I am getting an error.
查询:
select case when S.R1 = '6' then 5 when S.R1 = '7' then 6 when S.R1 = '8' then 7 when S.R1 = '9' then 8 when S.R1 ='10' then 9 else S.R1 end as Q FROM [HelpService].[dbo].[help] s ----------------------------------------------- SELECT [Source], [Score] INTO #Temp_Q FROM [HelpDesk].[dbo].[Survey] WHERE [data_Source Name] = 'Text Data' ----------------------------------------------- select CONVERT(REAL, a.[Dell Score]) as Q FROM [HelpService].[dbo].[help] s LEFT OUTER JOIN #CE_Temp_Q a on s.[R1] = a.[Source]
错误
消息 8114,级别 16,状态 5,第 1 行
Msg 8114, Level 16, State 5, Line 1
将数据类型 varchar 转换为 real 时出错.
Error converting data type varchar to real.
我被要求做的是我需要删除硬编码值并需要使用临时表编写查询.
What I am asked to do is I need to remove the hard coded values and need to write queries with a temp table.
提前致谢,夏什拉
推荐答案
将数据类型 varchar 转换为 real 时出错
Error converting data type varchar to real
这意味着您的一个值包含一些不是数字的东西.
This means one of your values contains somthing that isn't a Number.
例如以下工作正常
SELECT convert(Real, '1') UNION SELECT convert(Real, ' ') UNION SELECT convert(Real, NULL) UNION SELECT convert(Real, '123.123') UNION SELECT convert(Real, ' 456 ')
但是以下任何一项都会产生与您相同的错误
? But either of the following will yield the same error you are getting
SELECT convert(Real, ' 456 ') SELECT CONVERT(Real, '1 2')
更新
有时问题值是什么并不那么明显
Sometimes its not so obvious what the problem values are
尝试以下方法找到它
SELECT DISTINCT a.[Dell Score] FROM [HelpService].[dbo].[help] s LEFT OUTER JOIN #CE_Temp_Q a on s.[R1] = a.[Source]
或
SELECT DISTINCT a.[Dell Score], DATALENGTH (a.[Dell Score]) FROM [HelpService].[dbo].[help] s LEFT OUTER JOIN #CE_Temp_Q a on s.[R1] = a.[Source]