Menu
I would like to know how to use the IFNULL() BigQuery Standard SQL function properly. This is my current data structure. The columns named "key" and "stringColumn" store strings. Meanwhile, the column named "integerColumn" stores integers:
I would like to create a new column named "singleValueColumn" that takes the value of the "stringColumn" or "integerColumn" that is not null:
This is my BigQuery Standard SQL query:
SELECT key,
value.string_value as stringColumn,
value.int_value as integerColumn,
IFNULL(value.string_value, value.int_value) as singleValueColumn
FROM `com_skytracking_ANDROID.app_events_*`,
UNNEST(event_dim) as event,
UNNEST(event.params) as event_param
WHERE event.name = "order_event"
However, when I run the query I am getting this error:
Error: No matching signature for function IFNULL for argument types: STRING, INT64. Supported signature: IFNULL(ANY, ANY) at [4:9]
Thanks for your help.
786
BigQuery IFNULL() FunctionIf expr is NULL, return null_result. Otherwise, return expr. If expr is not NULL, null_result is not evaluated. expr and null_result can be any type and must be implicitly coercible to a common supertype.
IS_NAN DescriptionReturns NULL for NULL inputs.
The syntax is as follows: Select * from table_source where column is not NULL; If you want to read more about the where operator, please refer to the documentation. In addition if you want to replace the null values, you can use the IFNNULL() function.
BigQuery supports the Google Standard SQL dialect, but a legacy SQL dialect is also available. If you are new to BigQuery, you should use Google Standard SQL as it supports the broadest range of functionality. For example, features such as DDL and DML statements are only supported using Google Standard SQL.
Check this doc. I think you need to cast the int_value as a string:
IFNULL(value.string_value, CAST(value.int_value AS STRING)) AS singleValueColumn
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
