August 17, 2015 at 10:59 am
When I insert the following into my query...I get an error
,max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] else '' end)
TotalPaid is a field that contains, for example, 445.45
The message I receive is 'Error converting data type varchar to numeric'
but, this case statement works:
,max(case when patins.rank = 1.0 then INSCOMP.ORG else '' end)
Inscomp.Org just gives the insurance company
August 17, 2015 at 11:14 am
cory.bullard76 (8/17/2015)
When I insert the following into my query...I get an error,max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] else '' end)
TotalPaid is a field that contains, for example, 445.45
The message I receive is 'Error converting data type varchar to numeric'
but, this case statement works:
,max(case when patins.rank = 1.0 then INSCOMP.ORG else '' end)
Inscomp.Org just gives the insurance company
Quick suggestion, change
,max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] else '' end)
to this
,max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] else 0 end)
😎
August 17, 2015 at 11:19 am
Wow, that was easy.. haha....thanks!
August 17, 2015 at 11:21 am
cory.bullard76 (8/17/2015)
When I insert the following into my query...I get an error,max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] else '' end)
TotalPaid is a field that contains, for example, 445.45
The message I receive is 'Error converting data type varchar to numeric'
but, this case statement works:
,max(case when patins.rank = 1.0 then INSCOMP.ORG else '' end)
Inscomp.Org just gives the insurance company
CLAIMS.[TOTALPAID] is defined as a numeric data type so when using CASE SQL Server expects to output a numeric and '' is a varchar value that can't be converted to a numeric. I would change the '' to NULL because it won't be returned as the MAX.
Edit: Missed Eirikur's post. I wouldn't do 0 unless you are sure the MAX for valid TOTALPAID's will be > 0. Otherwise you will get an invalid max.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply