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

0
votes

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.

2
sqlgoogle-bigquery
UNION requires both queries being put together to return the same number of columns, with pairs of columns that have the same datatypes. You would need to ensure that the columns returned by your queries comply to that constraint... - GMB
Will edit question based on your input. Please see question. - noobsmcgoobs

2 Answers

2
votes

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
0
votes

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………