BigQuery INNER JOIN with TIMESTAMP and DATE types

0
votes

I'm trying to query two different tables in Google BigQuery. In my first table (smgradi) i have two fields:

  • data -> TIMESTAMP
  • ConsumoKwh -> INTEGER

In my second table (meteodata) i have other two fields:

  • DATA -> DATE
  • TMEDIA___C -> INTEGER

I want to merge those tables with tables, but I have to convert the first TIMESTAMP column into a DATE column.

I tried with this query:

SELECT smgradi.data, smgradi.ConsumoKwh, meteodata.TMEDIA___C
FROM energy_unitus.smgradi AS smgradi
INNER JOIN energy_unitus.meteodata AS meteodata
  ON DATE(smgradi.data) = meteodata.DATA

But it gives the following error:

Query Failed Error: ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table name. .

I also tried to compare the columns without "DATE", but it obviously return a blank table. Can you help me with this? Thank you, Paolo

1
sqlgoogle-bigquery

1 Answers

0
votes

This error is a limitation of Legacy SQL dialect. To get around it in Legacy SQL, you would need to do type transformation in subquery:

SELECT smgradi.data, smgradi.ConsumoKwh, meteodata.TMEDIA___C
FROM (SELECT DATE(data) data, ConsumoKwh FROM energy_unitus.smgradi) AS smgradi
INNER JOIN energy_unitus.meteodata AS meteodata
  ON data = meteodata.DATA

But also, please consider using Standard SQL, which doesn't have such limitation.