CODEWITHSUNDEEP
google-bigquery
Thiago Guimarães
Thiago Guimarães

Reputation: 35

_TABLE_SUFFIX on subselect

I need to using _TABLE_SUFFIX as parameter into subselect

SELECT 
  A._TABLE_SUFFIX,
  (
    SELECT
      COUNT(*)
    FROM
      `analytics_202222094.events_*` AS B
    WHERE
      B._TABLE_SUFFIX = A._TABLE_SUFFIX
      AND B.event_name = 'session_start' 
  )
FROM `analytics_202222094.events_*` AS A 
GROUP BY A._TABLE_SUFFIX

Upvotes: 2

Views: 419

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

SELECT 
  _TABLE_SUFFIX  AS table_suffix,
  COUNTIF(event_name = 'session_start')
FROM `analytics_202222094.events_*` 
GROUP BY _TABLE_SUFFIX

As you can see you don't need subquery here at all - but in cases when you will need - use alias for reserved field names started with underscore - like _TABLE_SUFFIX, _PARTITION, _TABLE_ , _FILE_, etc.

Upvotes: 2

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Give it an alias:

SELECT MAX(id), ts
FROM (
  SELECT *, _table_suffix ts
  FROM `fh-bigquery.stackoverflow_archive.201703_*` 
) a
GROUP BY 2

Otherwise you'll get the error

Invalid field name "_table_suffix". Field names are not allowed to start with the (case-insensitive) prefixes _PARTITION, TABLE, FILE and _ROW_TIMESTAMP

Upvotes: 0

Related Questions