Varchar to Float - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Varchar to Float

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 22:42:42

I get this error when I run this query below.

Error: Error converting data type varchar to float.

select
a.id as id ,
a.code as Code ,
a.number as Number ,
Case when b.groupcode in ('ABCD') then B.groupcode
when a.code ='PBSE' then 'PBSE' end as Groupcode

from REZAKOD01.dbo.ZooFee A

inner join zoo B
on A.code = B.code

Where b.GroupCode IN
(
'ABCD'
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:46:33

in a case expression all paths return type should be same . i guess B.groupcode is of type float. so when a.code ='PBSE' it also tries to convert string 'PBSE' to float type hence the error. If you want output to show string in one of path, then cast other value as to string like

Case when b.groupcode in ('ABCD') then CAST(B.groupcode as varchar(30))
when a.code ='PBSE' then 'PBSE' end as Groupcode

but keep in mind that its no longer a float so if you're using it for sorting or other manipulation it will regard it only as a string not numeric value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 22:49:12

Do you think you could either post what the datatypes are for each column, or give us a 1 or two values for each of the columns?

EX 1:
a.id = int
a.code = varchar
a.number = int
b.groupcode=varchar

Or if you're not sure of the datatypes, something like this:

a.id -> 100
a.code= C
a.number -> 50
b.groupcode -> 'ABCD'

Something's going where you're trying to make a character into a number and the character is not numeric.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 22:49:28

VIS! HOw do you type that fast!!!! :)

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 22:51:27

still the same error "Error converting data type varchar to float."

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 22:54:23

a.id -> 123456789
a.code= ABC
a.number -> 1
b.groupcode -> 'ABCD'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:55:36

is this statement part of UNION or UNION ALL?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 22:55:54

Peace, can you just do this:

Select top 10 a.id, a.code, a.number, b.groupcode
from REZAKOD01.dbo.ZooFee A

inner join zoo B
on A.code = B.code

Where b.GroupCode IN
(
'ABCD'
)

and post your results? It would be very helpful to see what your results are.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:56:50

quote:
Originally posted by flamblaster

VIS! HOw do you type that fast!!!! :)

Experience

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 22:58:30

Yeah, it's fun trying to keep up :)

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 23:02:33

flamblaster,

this is my results

Error converting data type varchar to float.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 23:02:50

flamblaster,

this is my results

Error converting data type varchar to float.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 23:04:45

let me try cast every each column

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 23:08:29

I bet your "code" column is the culprit. Is it possible that code is varchar in one table but numeric in another?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 23:12:19

quote:
Originally posted by flamblaster

I bet your "code" column is the culprit. Is it possible that code is varchar in one table but numeric in another?

it may even possible that OP is using this as a part of UNION ALL and order of columns in other statements are not same as this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 23:13:50

Yeah, that makes sense...I can't get it to error out with his/her select.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 23:15:48

here is the place need to cast:

inner join zoo B
on cast(A.code varchar(30)) = cast(B.code varchar(30))

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 23:18:38

What values are in A.code and B.code? Can you just give a few? Also, like Vis mentioned, if this is part of a union, intersect or except type query, you'll get errors if you're trying to put both number and characters into the same column. Any sample data would be helpful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 23:21:10

quote:
Originally posted by peace

here is the place need to cast:

inner join zoo B
on cast(A.code varchar(30)) = cast(B.code varchar(30))

so does that mean code has both varchar and float values? or is it that code is float in one table and varchar in other?even if thats case it wont be able to convert those non numeric values to float and will throw this error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-09-20 : 23:44:22

float..
ya, there are union..

i just paste down the code which error occur..

Thanks for all ur help guys!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 23:52:36

then make sure corresponding columns in all selects separated by union are of same datatype.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources