How to apply ifnull inside case when statement in SQL [BQ]?-CodePudding

How to apply the ifnull statement inside case when to avoid creating separate 'field_1' columns in the table? input:

output:

SELECT ifnull(field_1, 'other'), count(*),
        case when field_1 = 'done'
        and market in ('usa', 'ca')
        and date < TIMESTAMP('2015-01-01')
        then
        'historical'
        else field_1 end as field_1
     FROM `tablename` 
     group by 1,3

CodePudding user response：

You can use IS NULL in the conditional statement like this:

SELECT 
  CASE  
    WHEN field_1 = 'done' AND market IN ('usa', 'ca') AND date < TIMESTAMP('2015-01-01') THEN 'historical'
    WHEN field_1 IS NULL THEN 'other'
    ELSE field_1
  END AS field_1
FROM your_table