BigQuery: Checking for null in conditional expression

Question

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

Answer 1

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.