01 Sep 20 · npack · #bigquery ·   Bookmark   ×

BigQuery Correlated subqueries that reference other tables are not supported

Correlated Subquery is not supported in BigQuery. You will need to rewrite it to a join.

Example:

(SELECT DISTINCT video_id, 
COALESCE(custom_id,
(SELECT custom_id FROM `dataset.channel_map` b
WHERE a.channel_id = b.channel_id LIMIT 1),
'Default')
FROM `dataset.revenue` a)
ERROR: Correlated subqueries that reference other tables are not supported 
unless they can be de-correlated, such as by transforming them into 
an efficient JOIN

Solution:

Convert the correlated subquery into its own join as shown below

SELECT DISTINCT video_id, 
  COALESCE(
    a.custom_id, 
    b.custom_id, 
    'Default'
  )
FROM `dataset.revenue` a
LEFT JOIN `dataset.channel_map` b
ON a.channel_id = b.channel_id


npack

posted on 01 Sep 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds