CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
donviti
donviti

Reputation: 294

Case Statement, conversion failed when converting varchar value to data type int

I don't know how to properly explain this. In my Detail Table I am trying to find the Fees. Let's say the fees all equal $10.00

What I'm trying to do is simply make the field value negative when the tranname = 'Corr Official' and the Fee.Waiver is Null . Problem is, the field in the table is varchar. I've tried to do a cast and a convert with no success. 

(Case when Detail.FieldValue = '$0.00' and tranname = 'Official' then Fee.Waiver * -1 
when TranName = 'CORR-Official ' and Fee.Waiver IS not null then Fee.Waiver 
when TranName = 'CORR-Official' and Fee.Waiver  IS null then Cast(Detail.FieldValue, int) * -1 
when Detail.FieldValue = '$0.00' and Fee.Waiver IS not null then Fee.Waiver * -1 
when Fee.Waiver IS not null then Fee.Waiver * -1 
when Detail.FieldValue is null and Fee.Waiver IS null then Detail.FieldValue     
else Detail.FieldValue end) as FieldValue

Here is my error:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '$10.00' to data type int.

Upvotes: 2

Views: 5108

Answers (1)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32700

You can't convert $0.00 to 0, because to SQL that's not a number.

Since the design seems to be out of your control, and assuming you only ever have a dollar sign, and not any other currency signs, you can strip out the dollar sign before converting.

On your third line, do this:

when TranName = 'CORR-Official' and Fee.Waiver  IS null then 
    Cast(Replace(Detail.FieldValue, '$', '') as decimal(18, 2)) * -1

Or if you have multiple currencies (which it doesn't look like, but hey, let's be thorough):

when TranName = 'CORR-Official' and Fee.Waiver  IS null then 
    Cast(RIGHT(Detail.FieldValue, len(Detail.FieldValue) - 1) as decimal(18, 2)) * -1

Note, however, that you'll need to convert to a decimal as well, instead of an integer, otherwise you'll get:

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value '0.00' to data type int.

That said, it's always a much better idea to normalize the data and use appropriate data types.

Upvotes: 4

Related Questions