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 \nunless they can be de-correlated, such as by transforming them into \nan efficient JOIN\n

Solution:

Convert the correlated subquery into its own join as shown below

SELECT DISTINCT video_id, \n  COALESCE(\n    a.custom_id, \n    b.custom_id, \n    'Default'\n  )\nFROM `dataset.revenue` a\nLEFT JOIN `dataset.channel_map` b\nON a.channel_id = b.channel_id\n

mCollins

posted on