問題描述

首先祝大家新年快樂.我在編寫查詢時遇到問題.執行查詢時出現錯誤.

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]

這篇關于使用臨時表刪除硬編碼值的問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！