UNION ALL producing error Conversion failed when converting the varchar value '`0' to data type int.

Question

_{Wednesday, July 12, 2017 2:58 PM}

How is it possible that when I run the 1st half of the UNION ALL it runs fine and produces a result set and when I run the 2nd half of the UNION ALL it runs fine but when I combine with a UNION ALL it is producing the error message...

Conversion failed when converting the varchar value '`0' to data type int.

Is there any way around this? Any way to trouble shoot this?

Thanks for your review and am hopeful for a reply.

All replies (5)

_{Wednesday, July 12, 2017 3:01 PM}

You need to make both sets with the same data type.

Try to use cast(inttypecol as varchar(10)). You may need to adjust the size,

_{Wednesday, July 12, 2017 3:04 PM}

But which data column or do I have to play the elimination game to try and figure out which column is causing the error?

_{Wednesday, July 12, 2017 3:10 PM}

Your problem is that the varchar value has a little accent mark that won't allow it to convert.

A couple solutions, each with benefits and problems.

First you need to identify the column that causes the problem.  My approach is to comment out half of the columns in the UNION ALL (from both sides).  If I get the error, then the problem is with the columns not commented out and if no error, the problem is with the commented out columns.  I do that again until only one column is left (or I have reasonable guess which column is causing problem).

Fix 1: cast the integer column to varchar so that it has the same type as your value above.

Fix 2:  null the varchar values if they can't convert to number.

declare @t varchar(2) =  '`0' ;

select case when isnumeric(@t) = 0 then null else @t end
, case when patindex('%[^0-9]%', @t) > 0 then null else @t end

Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

_{Wednesday, July 12, 2017 3:11 PM}

Did you see this one is not a 0  but a string: `0 with single quotations wrapped.

You need to check both queries to make sure all columns are matching to the other set.  

If you post your table DDLs, other people may be able to help. But without them, you need to figure it out.

Start with int type columns in your queries.

_{Wednesday, July 12, 2017 9:57 PM}

How is it possible that when I run the 1st half of the UNION ALL it runs fine and produces a result set and when I run the 2nd half of the UNION ALL it runs fine but when I combine with a UNION ALL it is producing the error message...

Conversion failed when converting the varchar value '`0' to data type int.

Is there any way around this? Any way to trouble shoot this?

Yes, there is a workaround: be more judicious about your data types.
The reason this happens is that one (or more) of your columns in the first query is an integer column, whereas the corresponding colunm in the second query is a varchar column. (Or vice versa.) As long as you run the queries separately, there is no problem, as there is no need for conversion.

But when you combine them with UNION ALL, the resulting query must return the same data type for all rows. For this to happen, SQL Server employs its datatype precedence, and the type with lowest precedence is converted to the type with higher. In this case varchar has lower precedence than int, and unfortunately, there is an implicit conversion. (Hadn't there been, you would have gotten an error on the spot.)

Mixing data types casually will do you no good, and you will run into misery sooner or later. If a column is supposed to hold numeric data, use a numeric data type and not varchar.

In the meanwhile, check the data types from the two queries, and in the case where varchar meets int, run this:

  SELECT * FROM tbl WHERE col IS NOT NULL AND try_cast(col AS int) IS NULL

to spot the ugly ones.