Getting Error while executing

I am getting an error while eleminating the hard coded values.

Instead of this case statement I need to write simple select statement...using temp tables...

select case [BVG]
   when 1 then 1
   when 2 then 2
   when 3 then 3
end as Q0,

SELECT  CASE [AVG]
   when 1 then 1
   when 1.33 then 2
   when 1.5  then 2
   when 1.67 then 3
   when 2 then 3
   when 2.33 then 4
End as Q开发者_C百科

FROM [MS].[BE].[Survey]

So I have written a code using temp table.....

SELECT [Source], [Score] 

INTO #Temp_Table

FROM [MS].[dbo].[S_Survey]

WHERE [data_Source] = 'USA'

Instead of that case statement I am replacing this select statement....

SELECT  q.[Score] as Q --- Getting error in this place.Data Type is varchar (100).

FROM [MS].[BE].[Survey] s

LEFT OUTER JOIN #Temp_Table q on

s.[AVG] = q.[Source]

But I am getting an error while executing.... And the error is

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.

Instead of that place near q.[score] as Q what can I write.... and how can I write the syntax...

Thanks, Sahsra

You can't average varchars.

Try this

SELECT [Source] , cast( [Score] as decimal (10,2) )--or whatever you need it to be

INTO #Temp_Table

FROM [MS].[dbo].[S_Survey]

WHERE [data_Source] = 'USA'

I'm guessing that s.AVG is a float and q.Source isn't. This would cause SQL Server to do an implicit conversion on the JOIN ON s.[AVG] = q.[Source]and one of your values isn't a character. You should take another look at either my answer or Martin's the last time you had this problem.

e.g.

SELECT  q.[Score] 
FROM [MS].[BE].[Survey] s
WHERE like Score '%[^0-9.]%'

UPDATE If you need to remove the four character value 'NULL' you could probably do the following (haven't tested)

SELECT  q.[Score] as Q --- Getting error in this place.Data Type is varchar (100).

FROM [MS].[BE].[Survey] s

LEFT OUTER JOIN #Temp_Table q on

s.[AVG] = q.[Source]
and q.[Source] <> 'NULL'

Or you could not insert them into the #Temp_Table to begin with.

Or you could delete those values before you try and JOIN.

Of course this makes me think why aren't you using a Numerical type on the #temp_table to begin with?