Conversion Failure-Varchar value to int

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

Conversion Failure-Varchar value to int

Author

Topic

stharish
Starting Member

43 Posts

Posted - 2011-11-25 : 13:06:23

Not sure how to get around this issue. I'm very new to coding and SQL. I'm trying to join the two tables on the following columns so I can display the LookupValue in a Report Builder report.

RotationFlags (int, not null)
UserColumn2 (varchar(100), null

Select *

From tm_UserRotations

INNER JOIN tl_Lookups
ON tl_Lookups.UserColumn2=tm_UserRotations.RotationFlags

(Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'True' to data type int.)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 13:14:07

UserColumn2 has 'True' in at least one row - you are trying to compare with a ionteger value so get an error (int has precedence over varchar so it will convert to int)

What do you want to happen in this situation? Or are you joining on the wrong columns?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stharish
Starting Member

43 Posts

Posted - 2011-11-25 : 13:22:20

Ahh...I see! There is one row in the table that is true/false. I actually don't need to use that row at all. Any way to ignore that row? Any other suggestion?

Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 13:24:53

ON case when tl_Lookups.UserColumn2 like '%[^0-9]%' then tl_Lookups.UserColumn2 end =tm_UserRotations.RotationFlags

or you could use a derived table to exclude it - you can't add an "and" to the statement because you can't control the order of execution of that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stharish
Starting Member

43 Posts

Posted - 2011-11-25 : 13:33:35

Still getting the same error message. Am I doing something else wrong? Here's what I have now:

Select *

From tm_UserRotations

INNER JOIN tl_Lookups
ON case when tl_Lookups.UserColumn2 like '%[^0-9]%' then tl_Lookups.UserColumn2 end =tm_UserRotations.RotationFlags

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 13:44:55

doh!

ON case when tl_Lookups.UserColumn2 not like '%[^0-9]%' then tl_Lookups.UserColumn2 end = tm_UserRotations.RotationFlags

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources