conversation failed - 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

SQL Server 2005 Forums

Transact-SQL (2005)

conversation failed

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-13 : 16:20:33

Hi

I have this code...


SELECT        rt.RangeDescription, COUNT(dbo.HealthDataAnswer.RangeValue) AS TotalSum
FROM            dbo.HealthDataAnswer INNER JOIN
                         dbo.ConditionRange AS rt ON dbo.HealthDataAnswer.RangeValue BETWEEN rt.StartValue AND rt.EndValue INNER JOIN
                         dbo.SurveyAnswerInfo ON dbo.HealthDataAnswer.SurveyAnswerInfoID = dbo.SurveyAnswerInfo.SurveyAnswerInfoID INNER JOIN
                         dbo.Users ON dbo.SurveyAnswerInfo.UserID = dbo.Users.UserID INNER JOIN
                         dbo.Gender ON dbo.Users.GenderID = dbo.Gender.GenderID

Some values that I check within the between value are string and for example "23,6" this cause the query to fail beacase it cannot convert that value so it can check if the value are between Startvalue and Endvalue (which doesn't have decimals). What do I need to do to fix this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-13 : 16:31:11

try:

replace(dbo.HealthDataAnswer.RangeValue,',','.') BETWEEN rt.StartValue AND rt.EndValue

Too old to Rock'n'Roll too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-13 : 16:46:37

Sorry, I then get an error that it couldnt convert the "23.6" value...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-13 : 16:49:13

what are the datatypes of RangeValue, StartValue and EndValue and what are example values for all of them?

Too old to Rock'n'Roll too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-13 : 17:07:45

They are of type "int" and examples are...

StartValue EndValue
0 34
35 39
39 999

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-13 : 17:13:43

try:

convert(int,convert(decimal(12,4),replace(dbo.HealthDataAnswer.RangeValue,',','.'))) BETWEEN rt.StartValue AND rt.EndValue

Too old to Rock'n'Roll too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-13 : 17:37:06

Thanks, that worked :)

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-14 : 06:58:15

Hi again, if the rangevalue is equal to '' what do I need to change in order to get this to work for that case? I ran the query now on rows that had empty values, and then the conversation fails...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-14 : 07:42:26

Maybe this to ignore that values:

convert(int,convert(decimal(12,4),nullif(replace(dbo.HealthDataAnswer.RangeValue,',','.'),''))) BETWEEN rt.StartValue AND rt.EndValue

Too old to Rock'n'Roll too young to die.

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-14 : 07:48:08

Perfect, thanks again :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources