| Author |
 Topic  |
|
|
magmo
Constraint Violating Yak Guru
465 Posts |
 Posted - 03/13/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/13/2013 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 03/13/2013 : 16:46:37
|
| Sorry, I then get an error that it couldnt convert the "23.6" value... |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/13/2013 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 03/13/2013 : 17:07:45
|
They are of type "int" and examples are...
StartValue EndValue
0 34
35 39
39 999 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/13/2013 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 03/13/2013 : 17:37:06
|
| Thanks, that worked :) |
|
|
|
magmo
Constraint Violating Yak Guru
465 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/14/2013 : 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
Constraint Violating Yak Guru
465 Posts |
Posted - 03/14/2013 : 07:48:08
|
| Perfect, thanks again :) |
|
|
| |
Topic |
|
conversation failed
