0

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.

6
  • 1
    I blame NOLOCK ;) Oct 22, 2021 at 22:39
  • 4
    What are the types of columns B, C, ...Z? Do they match? My bet would one one of the column pairs having different types. Oct 22, 2021 at 22:41
  • 2
    In addition to what ypercube said, also make sure you didn't accidentally leave out a comma between one of the columns such that it's treating the proceeding column as an alias. E.g. SELECT ColumnA, ColumnB ColumnC results in ColumnB being called ColumnC actually.
    – J.D.
    Oct 22, 2021 at 23:24
  • Stop splattering your code with nolock. But if you do, AT LEAST use the current syntax and not the deprecated syntax.
    – SMor
    Oct 23, 2021 at 13:43
  • I can put in readuncommitted if you prefer :). That aside, the query has been working for many years and all of a sudden it started to error. As far as I know, nothing has changed query wise. So, the columns match up. The really strange thing, as I mentioned, I can run it using the two methods and not get an error.
    – steveb
    Oct 23, 2021 at 23:22

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.