CODEWITHSUNDEEP
sqlgoogle-bigquery
user_1357
user_1357

Reputation: 7940

BigQuery: Checking for null in conditional expression

Is it possible to perform a conditional if statement to collapse 3 fields into a single field? I am trying to achieve following. field1, field2 and field3 are are Int64 which is nullable. I could not find a way to do a null check so am checking if a positive value is assigned to any of the fields then set the a field to respective values. When syntax below I am getting error below:

case when field1 >= 0 then 0 end as field, 
case when field2 >= 0 then 1 end as field,
case when field3 >= 0 then 2 end as field

Duplicate column names in the result are not supported. Found duplicate(s): field

Upvotes: 3

Views: 14516

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Is this what you want?

(case when field1 is not null then 0
      when field2 is not null then 1
      when field3 is not null then 2
 end) as null_check

Or, if you want to turn this into a coded field:

concat(case when field1 is not null then 0 end,
       case when field2 is not null then 1 end,
       case when field3 is not null then 2 end,
      ) as null_check

This will list "0", "1", "2" in a string, depending on the values that are not NULL.

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

It sounds like perhaps you just want a CASE expression which can generate multiple values based on multiple conditions:

CASE WHEN field3 >= 0 THEN 2
     WHEN field2 >= 0 THEN 1
     WHEN field1 >= 0 THEN 0 END AS field

The logic here is that it will check field3 first, then field2, followed by field1, when determining which output to generate.

Upvotes: 1

Related Questions