Merge statement?

I am more of beginner with sql but would like some help on which statement would be best to use for my query. So I have an app that has test data, because the score could be 90 or be 85.6 the values are in different columns – former in int.value, latter in double.value. I need to merge the two columns together into one column for “test_score”. Here is my current query, data goes to a table called “App_test_outcome”:

SELECT event_date, timestamp_micros(event_timestamp) as Timestamp, user_pseudo_id, geo.country, geo.region, geo.city, geo.sub_continent,
(select value.string_value from unnest (event_params) where key = "test_passed") as Test_outcome,
(select value.string_value from unnest (event_params) where key = "test_category") as Test_outcome_category,
(select value.double_value from unnest (event_params) where key = "test_score") as Test_outcome_score,
FROM `Appname.analytics_number.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20200201' AND '20201130' AND
event_name = "test_completed"

Would I need to make another query to then merge with the above query already in that table, or is there a way to run a query and merge the two columns together in one. As I would prefer the latter option if possible.

I did get an error message when trying to append a query with double.value to int.value but an error message appeared “invalid schema: Field Test_outcome_score has changed type from FLOAT to INTEGER”. Which makes me think I cannot merge the two columns anyway.

Any help would be great,

Many thanks,

Answer

Maybe IFNULL with CAST will help:

(select IFNULL(value.double_value, CAST(value.int_value AS FLOAT64)) from unnest (event_params) where key = "test_score") as Test_outcome_score

Advertisement

Answer