CODEWITHSUNDEEP
google-bigquery
Topde
Topde

Reputation: 581

Unsupported variable type: STRUCT<INT64>

I've got a query where I need to use a list of strings over and over in a query and would like to declare it once, the only thing is I've only got it working as it is in the second example and would like to not have to UNNEST

DECLARE
  list X DEFAULT (
8335, 9776, 11496);
  
  SELECT * FROM `dataset.table` WHERE quantity_sold IN list

X as not sure what type it'd have to be

DECLARE
  list ARRAY<INT64> DEFAULT [
8335, 9776, 11496];
  
  SELECT * FROM `dataset.table` WHERE quantity_sold IN UNNEST(list)

Upvotes: 0

Views: 857

Answers (2)

Ernandes Junior
Ernandes Junior

Reputation: 53

I had a problem simulating but with string. What I did was:

DECLARE LIST_VALUES ARRAY<STRING> DEFAULT ['Z', 'Y', 'X'];

IF(comp IN UNNEST(LIST_VALUES), UPPER('ITS OK'), UPPER('ITS NOT OK'))

I believe that for your problem, just change the type of the array and use unnest

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

this is the "trick" I am usually using in such cases

with my_variables as (
  select [8335, 9776, 11496] list1, ['a', 'b', 'c'] list2
  # note: this is just one row CTE   
)
select * 
from `dataset.table`, my_valiables 
where quantity_sold in unnest(list1) 
and something_else in unnest(list1)
and yet_another_one in unnest(list2)

Upvotes: 1

Related Questions