BigQuery - IFERROR for standardSQL

google-bigquery legacy-code
Ilja picture Ilja · Mar 18, 2018 · Viewed 11.8k times · Source

I'm about to migrate queries from Legacy to Standard in BQ. In Legacy I used to have x/y returning null if y=0, however, I'm stuck in StandardSQL with the error:

Error: division by zero

I'd like to apply something like IFERROR(x/y,null)

Is it available in StandardSQL?

Answer

Mikhail Berlyant picture Mikhail Berlyant · Mar 18, 2018

In standard SQL you can use SAFE_DIVIDE(x, y)
It is an equivalent to the division operator (/). Returns NULL if an error occurs, such as division by zero.

Related questions

Setting GOOGLE_APPLICATION_CREDENTIALS for BigQuery Python CLI

I'm trying to connect to Google BigQuery through the BigQuery API, using Python. I'm following this page here: https://cloud.google.com/bigquery/bigquery-api-quickstart My code is as follows: import os import argparse from apiclient.discovery import build from apiclient.…

Read More
Google BigQuery Delete Rows?

Anyone know of any plans to add support for delete parts of data from a table in Google Bigquery? The issue we have right now is we are using it for analytics of data points we collect over time. We …

Read More
Can we cast the type in BigQuery?

Following my query : SELECT SQRT((D_o_latitude - T_s_lat)^2+(D_o_longitude - T_s_long)^2)/0.00001 FROM [datasetName.tableName] I am getting the error as Error: Argument type mismatch in function SUBTRACT:'D_o_latitude' is type …

Read More