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.
>Solution :
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