Type mismatch when combining two SQL queries with UNION

Question

select a,b,c, from transmission t where t.filnename ='ABC'  
select d,e,f, from transmission t where t.filnename ='ABC'

I want the results as two rows together into one resultset as result will go into DFF report.

Output Should be:

a,b,c  
d,e,f

I have tried UNION but it is giving me this error:

Error converting data type varchar to bigint.

You can't UNION different column types, different count of column, etc. — pavel, Jun 10 '19 at 22:43
https://stackoverflow.com/questions/32049244/why-is-my-case-statement-requiring-the-then-part-to-be-of-data-type-int/32049306#32049306 — Felix Pamittan, Jun 10 '19 at 22:44

Ondra Žižka · Answer 1 · 2019-06-11T11:27:42.533

0

Give the the columns the same name using AS,

select d AS a, e AS b, f AS c,

and cast it to the common super-type. If your database supports that. For instance, MySQL has CAST: http://www.mysqltutorial.org/mysql-cast/

select CAST(d AS VARCHAR) AS a, ...

Or convert it to the common super-type (e.g. by parsing a VARCHAR column to a number).
And then join it with UNION.

Anyway, this practice is not too nice. I would advise to rethink the approach and stay with normalized data structure.

edited Jun 11 '19 at 11:27

answered Jun 10 '19 at 22:44

Ondra Žižka

43,948
41
217
277

2

Giving columns the same name doesn't change anything. A column name is inherited by the first select in UNION. – M. Kanarkowski Jun 10 '19 at 22:49
That is not exact. Some databases can be configured to match UNION columns by name. So in this example you would end up with 6 columns, half of each row wit `NULL`s. Some databases (e.g. older H2) even required the column names. – Ondra Žižka Jun 11 '19 at 11:25
And since the OP did not specify which database he uses, we can not state that "by SQL standard". – Ondra Žižka Jun 11 '19 at 11:26

score 0 · Answer 2 · answered Jun 10 '19 at 22:47

The data types of at least one of columns a, b, or c are varchar, and at least one of d, e, and f in the same position (first, second or third) are bigint. As a commenter says, you can cast the bigint to varchar to make it work.

When using UNION, columns appearing in the same position have to have the same (or at least compatible) types.

Type mismatch when combining two SQL queries with UNION

2 Answers2