使用临时表删除硬编码值的问题 [英] Problem in removing hardcoded values using temp table
问题描述
首先祝大家新年快乐.我在编写查询时遇到问题.执行查询时出现错误.
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]
这篇关于使用临时表删除硬编码值的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
