How do I fix this data type error?

Transact-SQL
,
1

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

2

Because CLAIMS.[TOTALPAID] is numeric your ELSE clause needs to be numeric too - you have a blank string which is what is raising the error. Note the message: "Error converting data type varchar to numeric". Change it to:

,max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] else 0.0 end)

and that should fix it.

In the second case INSCOMP.ORG is datatype String (Varchar / Char / similar) so it is OK to have a Blank String in the ELSE.

Sometimes SQL will make an implicit conversion - for example Blank String to INTEGER is usually OK, but it will not do that to a Numeric datatype.

1 Like
3

that was easy.... thanks

4

You could also use NULL instead of 0.0

5

If you could ever have negative values, 0.0 could be an issue. I would just leave off the ELSE as it's not required and will default to NULL:

,max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] end)
1 Like
6

Maybe the ELSE was intended to prevent MAX giving a warning if it encounters NULL values? (We deliberately avoid that situation here)

Might also be a problem with the APP displaying the values if it is asked to display NULL - e.g. maybe the user would prefer to see "zero" rather than whatever the APP displays for NULL - which might be "blank". A solution for that would be to use a COALESCE() or NULLIF() to force the MAX result to 0.0 when it is NULL

7

That's a very good point, which I had completely overlooked.

8

If you need a final result of zero rather than null, you can add the ISNULL() around the MAX() rather than in the max expression itself:

,isnull(max(case when patins.rank = 1.0 then CLAIMS.[TOTALPAID] end), 0)
1 Like
9

Still get a Warning from including a NULL value in the MAX process though - that would make our APP collapse (it would treat it as a critical error)