CODEWITHSUNDEEP
sqlgoogle-bigquery
HG K
HG K

Reputation: 327

How to mix IF and CASE in BigQuery SQL

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.

Upvotes: 2

Views: 551

Answers (4)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Consider below approach

select *, coalesce(
  field_two, field_three, 
  [100, 200, 300][ordinal(range_bucket(field_one, ['A','B','C']))]
  ) as new_field
from your_table

or with a little refactoring

with map as (
  select ['A','B','C'] find, [100, 200, 300] match
)
select t.*, coalesce(
  field_two, field_three, 
  match[ordinal(range_bucket(field_one, find))]
  ) as new_field
from your_table t, map

if applied to sample data in your question - both output

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

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;

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

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;

Upvotes: 0

Sergey Geron
Sergey Geron

Reputation: 10152

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

Upvotes: 0

Related Questions