CODEWITHSUNDEEP
google-bigquery
S.Nori
S.Nori

Reputation: 113

BigQuery Case Statement for Datetime doesn't work for Null values

I'm writing a simple case statement in BigQuery to check the value of a Datetime:

select
case datetime_field
when null then 'Open'
else 'Closed'
end
from a_table

There are null values in the datetime_field yet the case statement evaluates to 'Else'.

The following simple select statement does return the values:

select * from a_table where datetime_field is null

Any idea why then a Case statement doesn't evaluate null datetime?

Upvotes: 0

Views: 1186

Answers (1)

Ka Boom
Ka Boom

Reputation: 484

From the doc:

Compares expr to expr_to_match of each successive WHEN clause and returns the first result where this comparison returns true. The remaining WHEN clauses and else_result are not evaluated. If the expr = expr_to_match comparison returns false or NULL for all WHEN clauses, returns else_result if present; if not present, returns NULL.

and the doc

Unless otherwise specified, all operators return NULL when one of the operands is NULL.

So, when null is used in the case when, it means the datetime_field = null is evaluated and the result return NULL. The else claused is evaluated and return "Closed".

To fix this, use the IF function instead:

select if(datetime_field is null, "Open", "Closed") from a_table

Upvotes: 1

Related Questions