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.
Go to Top of Page

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...
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-13 : 17:37:06
Thanks, that worked :)
Go to Top of Page

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...
Go to Top of Page

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.
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-14 : 07:48:08
Perfect, thanks again :)
Go to Top of Page
   

- Advertisement -