CODEWITHSUNDEEP
google-bigquery
Alice Work
Alice Work

Reputation: 305

case when statement not working in bigquery

I'm getting the following error message when I do a case when statement:

No matching signature for operator CASE for argument types: BOOL, FLOAT64, 
STRING at [10:1]

the statement is:

case when a.datetime between '2015-11-01' and '2016-10-31' then 
a.i_sec_out_to_auth else 'NA' end as PY_resp,
case when a.datetime between '2016-11-01' and '2017-10-31' then 
a.i_sec_out_to_auth  else 'NA' end as CY_resp

Am I missing something. is google bigquery not handling case whens fully yet?

Keep in mind a.i_sec_out_to_auth is a column of type float in my dataset.

Upvotes: 1

Views: 10116

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

The problem with your code is that expected output in THEN and in ELSE are of different data type (respectivelly FLOAT and STRING)! But it should be the same Try below 

case when a.datetime between '2015-11-01' and '2016-10-31' then 
a.i_sec_out_to_auth else 0 end as PY_resp,
case when a.datetime between '2016-11-01' and '2017-10-31' then 
a.i_sec_out_to_auth  else 0 end as CY_resp

or 

case when a.datetime between '2015-11-01' and '2016-10-31' then 
a.i_sec_out_to_auth else null end as PY_resp,
case when a.datetime between '2016-11-01' and '2017-10-31' then 
a.i_sec_out_to_auth  else null end as CY_resp

Upvotes: 7

Related Questions