Problem in removing hardcoded values using temp table

Question

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.

Query:

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]

ERROR

Msg 8114, Level 16, State 5, Line 1

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.

Thanks in Advance, Shashra

Not enough information. What is the connection between your 2 queries? Which one gives the error? What are the schemas of your tables? — Martin Smith
– Martin Smith, Commented Dec 31, 2010 at 21:58
If the error is in the first select add where isnumeric(s.r1) =1 to your first query — u07ch
– u07ch, Commented Dec 31, 2010 at 23:19
@u07ch - That isn't guaranteed to solve the problem SQL Server is free to do the convert first and the filter later as per this question stackoverflow.com/questions/3088709/… — Martin Smith
– Martin Smith, Commented Dec 31, 2010 at 23:26

Conrad Frix · Accepted Answer · 2010-12-31 23:07:19Z

2

Error converting data type varchar to real

This means one of your values contains somthing that isn't a Number.

For example the following works fine

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')

UPDATE

Sometimes its not so obvious what the problem values are

Try the following to find it

SELECT DISTINCT 
        a.[Dell Score]
FROM 
      [HelpService].[dbo].[help] s
      LEFT OUTER JOIN #CE_Temp_Q a on
      s.[R1] = a.[Source]

OR

  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]

edited Dec 31, 2010 at 23:07

answered Dec 31, 2010 at 22:45

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shahsra Over a year ago

Thanks for replying me.. But I just checked that all the values comming to [DELL SCORE] are just 'integers' (1,2,3,4) and NULL's means ("NULL").

Conrad Frix Over a year ago

@Shahsra I've updated the answer. This should help identify the problem values. BTW I'm not sure what is meant by NULL's means ("NULL"). Are you trying to convert the literal 4 character string "NULL" to a real. Because that can't be converted to a real anymore than CONVERT(Real, 'Shahsra') can.

Martin Smith · Accepted Answer · 2010-12-31 23:15:46Z

1

What does the following query return?

select a.[Dell Score]
FROM [HelpService].[dbo].[help] s
LEFT OUTER JOIN #CE_Temp_Q a on
s.[R1] = a.[Source] 
WHERE  a.[Dell Score] like '%[^0-9.]%'

answered Dec 31, 2010 at 23:15

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Collectives™ on Stack Overflow

Problem in removing hardcoded values using temp table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related