I have a query with a couple of nested tables and all of them use a filter like this:
WHERE
DATE(my_time) IN ("2017-08-12", "2017-08-13", "2017-08-14", ..., "2017-08-30")
I am trying to declare a STRUCT type variable in the start of the query with all the dates so that I can later replace all filters with:
WHERE
DATE(my_time) IN struct_var
I am looking for something like this:
DECLARE struct_var STRUCT <"2017-08-12", "2017-08-13", "2017-08-14", ..., "2017-08-30">;
but I keep getting errors and can't figure out the syntax.
Anyone knows how to fix this?
Thanks
You don't want a struct. You want an array:
declare date_array array<date>;
set date_array = [date('2017-08-12'), date('2017-08-13'), date('2017-08-14')] ;
Of course, you wouldn't use in for this purpose. You could use:
where date(my_time) in (select d from unnest(date_array) d)