CODEWITHSUNDEEP
sqlgoogle-bigquery
Nik S
Nik S

Reputation: 105

Bigquery - Substitute field if other field is blank

I've got this code here: 

select DealID,ExternalReference,order_number, sales_rule
from flostream.orders
join mobileheads.surveys on flostream.orders.ExternalReference = mobileheads.surveys.order_number
//where DealID is null

What I want to happen is IF DealID (in flostream.orders) is null, replace it with sales_rule (in mobileheads.surveys)

Please let me know if this can be done with Bigquery or if you can think of some workaround?

Thanks!

Nik

Upvotes: 1

Views: 3606

Answers (1)

Pentium10
Pentium10

Reputation: 207838

You need

IFNULL(expr, null_default)  
If expr is not null, returns expr, otherwise returns null_default.

You should read more about them in the manual.

Your query would look like:

SELECT IFNULL(DealID,sales_rule) as DealID,
       ExternalReference,
       order_number,
       sales_rule
FROM flostream.orders
JOIN mobileheads.surveys ON flostream.orders.ExternalReference = mobileheads.surveys.order_number

Upvotes: 2

Related Questions