Conversion failed when converting the varchar valu

Question

I have been finding original balances on bonds from an "Acquisition" table, for individual "performance" quarters. It has worked for every quarter, but when I attempted to run my query on 2010Q1, I received this notice:

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

My query is:

SELECT ALL a.[LOAN IDENTIFIER]
      ,[CHANNEL]
      ,[SELLER NAME]
      ,[ORIGINAL INTEREST RATE]
      ,[ORIGINAL UNPAID PRINCIPAL BALANCE (UPB)]
      ,[ORIGINAL LOAN TERM]
      ,[ORIGINATION DATE]
      ,[FIRST PAYMENT DATE]
      ,[ORIGINAL LOAN-TO-VALUE (LTV)]
      ,[ORIGINAL COMBINED LOAN-TO-VALUE (CLTV)]
      ,[NUMBER OF BORROWERS]
      ,[ORIGINAL DEBT-TO-INCOME RATIO]
      ,[CREDIT SCORE]
      ,[FIRST-TIME HOME BUYER INDICATOR]
      ,[LOAN PURPOSE]
      ,[PROPERTY TYPE]
      ,[NUMBER OF UNITS]
      ,[OCCUPANCY STATUS]
      ,[PROPERTY STATE]
      ,[ZIP (3-DIGIT)]
      ,[MORTGAGE INSURANCE PERCENTAGE]
      ,[PRODUCT TYPE]
  FROM dbo.Performance_2010Q1 a join dbo.TotalAcquisition b on a.[Loan Identifier] = b.[LOAN IDENTIFIER]

Where a.[Zero Balance Code] = 97 or a.[Zero Balance Code] = 03

Zero balance code is telling how the bond defaulted, I am matching between the two tables based on each bonds' "Loan Identifier"

Thanks! :-D

Answer 1

Since you have no explicit conversion in the field list, it must be an implicit conversion either in the ON or in the WHERE clause.

For the ON clause to cause a conversion, Loan Identifier would have to be defined as nvarchar in one, and as an int in the other table. This is unlikely (though not impossible ;-)

So I suspect your WHERE clause. IF Zero Balance Code is defined as nvarchar and you are comparing with an int (97 or 3), that would cause an implicit conversion. If you have only one entry that contains characters in your table, this implicit conversion will fail. One way to avoid this is to define the WHERE clause with strings (e.g. WHERE A.[Zero Balance Code] = '97', the same for '03'). One slightly more elegant way is to check for numeric data with ISNUMERIC. Because how do you know that the balance code is always '03', and not only '3'? Those entries would be filtered out, because '3' is not equal to '03'...

But be careful:
This may be ignoring the real problem. The big question here is, why all of a sudden you have non numeric data in your table! You need to check if you somehow got corrupted data entered into your system.

Answer 2

Is there a value in a.[Zero Balance Code] that cannot cast to an int?

Try this for your where caluse:

Where a.[Zero Balance Code] = '97' or a.[Zero Balance Code] = '03'