CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

IFNULL and date

I have the following select statement 

FIRST_VALUE(date) OVER (PARTITION BY client_ID ORDER BY date ASC),0) AS first_conversion_date,

I'd like it to return a string or an integer if no date is available.

I tried this for example:

ifnull(FIRST_VALUE(date) OVER (PARTITION BY client_ID ORDER BY date ASC),0) AS first_conversion_date,

but got the following error:

No matching signature for function IFNULL for argument types: DATE, INT64. Supported signature: IFNULL(ANY, ANY) at [26:9] Learn More about BigQuery SQL Functions.

What should I do?

Upvotes: 0

Views: 1953

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Looks like you want to avoid nulls in your result

Obviosuly you cannot mix DATE type with STRING or INT64 in same output column So you need to CAST all into STRING as in below example 

IFNULL(CAST(FIRST_VALUE(DATE) OVER (PARTITION BY client_ID ORDER BY DATE ASC) AS STRING), 'whatever default value you have in mind') AS first_conversion_date

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Do you want coalesce()?

COALESCE(FIRST_VALUE(date) OVER (PARTITION BY client_ID ORDER BY date ASC),0)

Upvotes: 0

Related Questions