Error Converting Date Type nvarchar to Numeric

Hello.

I have a pretty simple Subquery that is giving me heartburn.
(Select Max(Case When ah.TransactionType = 'DCA' OR ah.TransactionType = 'DPA' Then ah.TransactionType Else '' End) From AccountHistory ah Where ah.MemberNumber = aud.MemberNumber AND ah.PrincipalAmount = aud.DebitAmount)

ah.PrincipalAmount is Numeric .. however, aud.DebitAmount isn't always numeric. I put the condition ISNUMERIC(aud.DebitAmount) = 1, but I am still getting an error.

I have replaced ah.PrincipalAmount = aud.DebitAmount with:

ah.PrincipalAmount = Convert(NVARCHAR(10),Case When IsnUmeric(aud.DebitAmount) = 1 Then aud.DebitAmount Else 0 End) AND

ah.PrincipalAmount = Cast(Case When IsnUmeric(aud.DebitAmount) = 1 Then aud.DebitAmount Else 0 End AS NVARCHAR(10))

Which gives me an error of Conversion failed when converting the nvarchar value ..... to data type int.

Where am I going wrong? Thanks for your assistance.

An empty string / blank is not a valid numeric value.
Thus, you need to change the '' in this to something else, perhaps 0.0?
...
Else '' End
...

Thanks for the response. That part of the subquery doesn't seem to have an impact on the Where section. I did change it to 0.00, but got the same result.

unless you provide sample data with specific data types, it is just guess work

How can you have a DebitAmount - that is non-numeric? The best way to resolve this is to fix the schema and not allow non-numeric data into a column that should be defined as numeric.

With that said - you should be able to solve this by cast/convert of the PrincipalAmount to nvarchar(nn). Convert it to the same definition of the DebitAmount column.

cast(ah.PrincipalAmount As nvarchar(10)) = aud.DebitAmount

Thanks Jeff! That worked.

It does seem illogical ... but the vendor sends is this awful text file and the DebitAmount field sometimes has alpha characters in it.

It also seems a bit unusual to be joining on an amount column - there really should be a better candidate key in that data to identify the specific row. But - if that works, great.

1 Like