CODEWITHSUNDEEP
google-bigquery
lufc
lufc

Reputation: 2040

BigQuery COALESCE() with SELECT subquery

I am getting the 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

On the following query

(SELECT DISTINCT video_id, 
                   COALESCE(custom_id, 
                             (SELECT custom_id FROM `test2.channel_map` b 
                              WHERE a.channel_id = b.channel_id LIMIT 1), 
                                'Default')
 FROM `test2.revenue` a)

I am essentially trying to replace null custom_ids with another custom_id from a lookup table. Is there a better way to do this that BigQuery will accept?

Upvotes: 0

Views: 2249

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

just use regular LEFT JOIN - something like below 

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

Upvotes: 3

Related Questions