How to mix IF and CASE in BigQuery SQL-CodePudding

When I have a table like this

field_one	field_two	field_three
A	1	1
A	2	2
A	null	3
A	null	null
B	1	1
B	null	4
B	null	null
C	1	1
C	null	null

I'd like to select a new field using this logic. (I wrote it in JS stytle for understanding.):

new_field = field_two

if ( field_two == null ) {
   new_field = field_three
}

if ( field_three == null ) {
   switch ( field_one ) {
       case 'A':
           new_field = 100
       case 'B':
           new_field = 200
       case 'C':
           new_field = 300
   }
}

Expected result:

field_one	field_two	field_three	new_field
A	1	1	1
A	2	2	2
A	null	3	3
A	null	null	100
B	1	1	1
B	null	4	4
B	null	null	200
C	1	1	1
C	null	null	300

Can I do this in a query in BigQuery??

I know COALESCE and CASE but not enough for the above logic.

CodePudding user response：

You may try using this select, assuming you just want to view the column new_field:

SELECT field_one, field_two, field_three,
       COALESCE(field_two,
                field_three,
                CASE field_one WHEN 'A' THEN 100
                               WHEN 'B' THEN 200
                               WHEN 'C' THEN 300 END)) AS new_field
FROM yourTable
ORDER BY field_one, field_two, field_three;

CodePudding user response：

Try this one:

select 
    *,
    coalesce(field_two, 
             field_three,
             case field_one when "A" then 100 when "B" then 200 when "C" then 300 END) as new_field
FROM my_table

CodePudding user response：

In SQL, IF is usually represented as CASE. Use a simple nested CASE for your problem

   Select field_one,    field_two,  field_three, 
   Case when field_two is NULL
   THEN field_three
   When field_three is NULL
   THEN 
            CASE WHEN FIELD_ONE ='A' 
            Then 100
            WHEN FIELD_ONE ='B' 
            Then 200
            WHEN FIELD_ONE ='C' 
            Then 300
            END
   END         
   AS
   new_field FROM TABLE;