Error converting data type varchar to numeric

1

Hello.

So here is what I have:

Convert(Varchar,Case When aud.DebitAmount Like '%-' Then '-'+replace(aud.DebitAmount,'-','') Else aud.DebitAmount End)

So this takes the aud.DebitAmount field and puts the negative sign in front of the number should one exist and otherwise makes the field a number.

Ran an "IsNumeric" test and everything comes back = 1.

But, when I use it in a subquery in the When section I get the referenced error.
(Select Max(ah.TokenString) From AccountHistory ah Where ah.MemberNumber = aud.MemberNumber AND ah.AccountNumber = Right(aud.AcctNumber,1) AND DateDiff(dd,ah.EntryDate,Cast(aud.ReportDate AS Date)) Between -5 AND 5 AND (ah.PrincipalAmount-1) = Convert(Varchar,Case When aud.DebitAmount Like '%-' Then '-'+replace(aud.DebitAmount,'-','') Else aud.DebitAmount End))*

ah.PrincipalAmount is numeric.

I slowly built the subquery and only came up with the error with the last section. Where am I going wrong?

Thank you.

2

Why are you converting to varchar if you need a number?

= CAST(replace(aud.DebitAmount,'-','') as decimal(19, 2)) * CASE WHEN aud.DebitAmount LIKE '%-' THEN -1 ELSE 1 END

3

Is the DebitAmount a varchar column and if so why is it a varchar?

4

Hmmmmmm. I'm not sure why I did it that way.

I tried your recommendation and was met w/ Error converting data type nvarchar to numeric

5

Then you've got bad data in that column. If you have TRY_CAST available (in your version of SQL is recent enough), you can use that to find all the bad values without the code crashing.