Getting Error while executing

Question

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

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

HLGEM · Accepted Answer · 2011-01-03 17:29:37Z

2

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'

answered Jan 3, 2011 at 17:29

HLGEM

97k15 gold badges120 silver badges191 bronze badges

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

4 Comments

Conrad Frix Over a year ago

[AVG] is the field name not the use of the Aggregate, so I don't think "You can't average varchars" applies here.

Shahsra Over a year ago

Thanks for giving me reply. But there is a problem if we put decimal....Becuase there is a single value 'NULL' in that column. If we put decimal it wont accept the 'NULL'...So what shall I do now...

Shahsra Over a year ago

[AVG] column is just a name they took...they are not aggregating anything there....

HLGEM Over a year ago

You should not be using 'Null' but NULL.

Community · Accepted Answer · 2017-05-23 10:33:08Z

0

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?

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Jan 3, 2011 at 17:41

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

4 Comments

Shahsra Over a year ago

Hi thanks for reminding me but I have already tried its not working in my case.... Because there are some values containing 'NULL' and some are 1,2,3 and some are 1.1,2.33,2.54 like this....

IamIC Over a year ago

"<> 'NULL'" doesn't look right. It should be 'IS NOT NULL' (unless the actual value is 'null', which is bad design).

Conrad Frix Over a year ago

@IanC Its the bad design one hence my explanation " If you need to remove the four character value 'NULL"

IamIC Over a year ago

I know... my comment was to the OP, not you :-)

Collectives™ on Stack Overflow

Getting Error while executing

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related