SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 conversation failed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

524 Posts

Posted - 03/13/2013 :  16:20:33  Show Profile  Reply with Quote
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
8765 Posts

Posted - 03/13/2013 :  16:31:11  Show Profile  Visit webfred's Homepage  Reply with Quote
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

524 Posts

Posted - 03/13/2013 :  16:46:37  Show Profile  Reply with Quote
Sorry, I then get an error that it couldnt convert the "23.6" value...
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/13/2013 :  16:49:13  Show Profile  Visit webfred's Homepage  Reply with Quote
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

524 Posts

Posted - 03/13/2013 :  17:07:45  Show Profile  Reply with Quote
They are of type "int" and examples are...

StartValue EndValue
0 34
35 39
39 999
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/13/2013 :  17:13:43  Show Profile  Visit webfred's Homepage  Reply with Quote
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

524 Posts

Posted - 03/13/2013 :  17:37:06  Show Profile  Reply with Quote
Thanks, that worked :)
Go to Top of Page

magmo
Aged Yak Warrior

524 Posts

Posted - 03/14/2013 :  06:58:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/14/2013 :  07:42:26  Show Profile  Visit webfred's Homepage  Reply with Quote
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

524 Posts

Posted - 03/14/2013 :  07:48:08  Show Profile  Reply with Quote
Perfect, thanks again :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000