Error Converting Date Type nvarchar to Numeric

Transact-SQL
1

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.

2

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
...

3

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.

4

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

5

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
6

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.

7

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