CODEWITHSUNDEEP
google-bigquery
S M
S M

Reputation: 101

Case statement in Bigquery

How to convert this IF statement in SQL using CASE statement

if address='US\Madison Drive' then 'ZIP1234'
elseif address='US\123 Madison Dr' then 'ZIP1234'
elseif address'US\123 Madison-Dr' then 'ZIP1234'
ELSE 'ZIP9999' END

I need to rewrite this statement using CASE statement in where condition

Select a.ID, NAME, Address, ZIP from 
table_A a left join table_B b
on a.ID = b.ID#
where {I need to put if condition mention above}

Upvotes: 0

Views: 286

Answers (2)

S M
S M

Reputation: 101

case address 
    when ('US' || CHR (92) || 'Madison Drive') then 'ZIP1234' 
    when ('US' || CHR (92) || '123 Madison Dr') then 'ZIP1234' 
    when ('US' || CHR (92) || '123 Madison-Dr') then 'ZIP1234' 
    else 'ZIP9999' end ``` 

**'\' was giving error**

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

How to convert this IF statement in SQL using CASE statement

case address 
  when 'US\Madison Drive' then 'ZIP1234'
  when 'US\123 Madison Dr' then 'ZIP1234'
  when 'US\123 Madison-Dr' then 'ZIP1234'
  else 'ZIP9999' 
end

another option for this particular example

case when address in (
    'US\Madison Drive', 
    'US\123 Madison Dr', 
    'US\123 Madison-Dr') 
  then 'ZIP1234'
  else 'ZIP9999' 
end

Upvotes: 2

Related Questions