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.
(SELECT DISTINCT video_id,
(SELECT custom_id FROM `dataset.channel_map` b
WHERE a.channel_id = b.channel_id LIMIT 1),
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
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
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