CODEWITHSUNDEEP
sqlgoogle-bigquery
noobsmcgoobs
noobsmcgoobs

Reputation: 2746

SQL Error in Google Big Query with UNION ALL on tables with same schema EDIT: change in schema from String to INT

I have the following query

 SELECT *
  FROM `January_2018`

UNION ALL

 SELECT *
  FROM `February_2018`

I get the following error on the second SELECT call

Column 14 in UNION ALL has incompatible types: STRING, STRING, INT64, INT64, INT64, INT64, INT64, INT64, INT64, INT64, INT64, INT64 at [7:3]

The column name is travel_type with a type of integer with values 0, 1 and 2.

I am trying to make one large table from several smaller ones - monthly tables of the same data. It seems that one of the fields has changed from String to Int data type after the 4th month and stays Int ongoing after that.

Upvotes: 0

Views: 3403

Answers (2)

Bharat Mishra
Bharat Mishra

Reputation: 91

TO select data from different tables you can use Wildcard instead of union. Wildcard will execute your query on all tables satisfying the condition. You can use wildcard ‘*’ with table prefix to select multiple tables at once. Your table names must have same Prefix with different suffix. Ex – Mytable_1, Mytabel_2, Mytable_3………

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Try the following so both table schemas match:

SELECT * EXCEPT(changed_column)
  , CAST(changed_column AS STRING) AS changed_column
FROM table1
UNION ALL
SELECT * EXCEPT(changed_column)
  , CAST(changed_column AS STRING) AS changed_column
FROM table2

Upvotes: 2

Related Questions