CODEWITHSUNDEEP
sqlcastinggoogle-bigquery
vtrhmd
vtrhmd

Reputation: 85

Casting as INT64 from CASE WHEN

Good afternoon. I have a table in which all the columns are in string format. To clarify, the 'null' is a string, not an empty value in the table.

install_date app_id value revenue
2021-01-01 id12345 0 'null'
2021-01-02 id12345 'null' 0
2021-01-03 id12345 1 5

I have to do two things: convert the 'null' string into 0, then cast that as int64. I have a query below, but I am receiving errors in relation to my syntax: Syntax error: Unexpected ").

SELECT 
install_date, 
app_id, 
cast((case when value = 'null' then '0' else value) as int64) as value
cast((case when revenue = 'null' then '0' else revenue) as int64) as revenue

Any idea what I should do? Thank you very much in advance.

Upvotes: 0

Views: 3264

Answers (2)

Alan Lacerda
Alan Lacerda

Reputation: 868

Assuming that you posted the actual query that you're trying to execute, I see that you're missing the END keyword on both your CASE statements and a , after value, on line 4.

SELECT
    install_date,
    app_id,
    CAST((CASE value WHEN 'null' THEN '0' ELSE value END) AS int64) AS value,
    CAST((CASE revenue WHEN 'null' THEN '0' ELSE revenue END) AS int64) AS revenue
FROM
    DATA

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270431

My guess is that you are getting type conversion errors. I would suggest instead:

select coalesce(safe_cast(value as int64), 0),
       coalesce(safe_cast(revenue as int64), 0)

Of course, you might want to investigate what other non-number values are in the column:

select distinct value, revenue
from t
where safe_cast(value as int64) is null or
      safe_cast(revenue as int64) is null;

Upvotes: 0

Related Questions