Strange Error ... Conversion failed when converting the varchar value 'www.com' to data type int

Question

The issue that I am running into is happening on Microsoft SQL Server.

I have a view that is unioning together 2 tables. It is returning a little over 7 million records.

When I attempt to JOIN together the view with another table on one column I am getting an error: "Conversion failed when converting the varchar value 'www.com' to data type int" Example code:

View_A:

SELECT ColumnA, ColumnB, …, ColumnZ
FROM Table1
UNION ALL
SELECT ColumnA, ColumnB, …, ColumnZ
FROM Table2

This SELECT is returning a Conversion failed error:

SELECT * FROM View_A (NOLOCK) INNER JOIN Table3 (NOLOCK) ON View_A .ColumnA = Table3.ColumnA

The ColumnA’s in all three tables are of type INT.

If I do one of the following, I get no errors:

1. SELECT * INTO #TempTable FROM ViewA (NOLOCK) and then do the JOIN on Table3
2. SELECT * FROM View_A (NOLOCK) INNER JOIN Table3 (NOLOCK) ON TRY_CAST(View_A .ColumnA AS INT) = Table3.ColumnA

Any ideas on what is happening and how to fix would be greatly appreciated.

Answer 1

Something, somewhere is doing an implicit conversion. Since int has a higher precedence than varchar the string "www.com" is being cast to int and failing.

First try a simple select * from view_a. Does that succeed? If it does it suggest table1 and table2 have compatible data types in their corresponding columns.

Second check the types assigned to columns of #TempTable. Are they what is expected?

Then check select * from view_A where TRY_CAST(View_A.ColumnA AS INT) is null. Are any rows returned? If so the view's column A is not what you think investigate that.

TRY_CAST returns NULL if the cast fails. I would expect rows that do not cast correctly to be omitted from the join in your second "I've tried".

If there's still no resolution create copies of tables 1, 2 and 3 with just column A. Do the views and queries work then? If so add back further columns, perhaps a few at a time if there are many, and repeat until the error is thrown.