CODEWITHSUNDEEP
google-bigquery
Brian Pressman
Brian Pressman

Reputation: 29

using a case when to identify if a timestamp = 'null'

I'm trying to construct a case when statement in BQ on a timestamp column called version_end_ts. Specifically I want my statement to read:

case when version_end_ts = 'null' then 'active' else 'past' end as status

Problem is BQ is is telling me that it "Could not cast literal "null" to type TIMESTAMP". Is there a way to return a positive flag in my case when if the timestamp is missing? I searched high and low and couldn't find an answer to this one.

Thank you in advance for your help.

Brian

version_end_ts

Upvotes: 0

Views: 5888

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below is yet another option (BigQuery Standard SQL) 

IF(version_end_ts IS NULL, 'active', 'past') AS status

It is matter of preferences - i usually prefer less verbose options

You can check above with dummy data as below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT NULL AS version_end_ts UNION ALL
  SELECT CURRENT_TIMESTAMP() 
)
SELECT 
  version_end_ts, 
  IF(version_end_ts IS NULL, 'active', 'past') AS status
FROM `project.dataset.table`

with result as 

Row version_end_ts                  status   
1   null                            active   
2   2018-08-16 05:24:53.221575 UTC  past

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522074

I couldn't find an exact duplicate for your question, at least not for BigQuery. In BigQuery (and most flavors of SQL), NULL is a special value, and checking for the presence/absence of a NULL value uses the special operators IS NULL and IS NOT NULL. So your CASE expression should look something like this:

CASE WHEN version_end_ts IS NULL THEN 'active' ELSE 'past' END AS status

The reason for why we need to use IS NULL rather than = is that NULL, by definition, represents a value which is not known. So it doesn't make sense to compare something not known with the = operator, because it would never be true.

Upvotes: 1

Related Questions