3

I have a simple query that runs successfully, but when I introduce a variable into the query, I am unable to save a view using that query. For example:

SELECT * FROM mytable WHERE color = 'red';

This query runs fine. Then:

DECLARE color STRING DEFAULT 'red';
SELECT * FROM mytable WHERE color = color;

This query also runs fine. Then in the BigQuery UI I click to "Save view", but I get an error saying Unexpected keyword DECLARE. Why is that?

flyingL123
  • 7,686
  • 11
  • 66
  • 135
  • Views are static, so I'm not sure why you want to use a variable in a view (as Gordon points out, this is not possible) but a stored procedure might be more in line with what you are trying to do: https://cloud.google.com/blog/products/data-analytics/command-and-control-now-easier-in-bigquery-with-scripting-and-stored-procedures – Nathan Griffiths Jan 15 '20 at 21:08
  • 1
    @NathanGriffiths the query I gave is just an example. In reality I need to reference the color `red` in a few places throughout the query, so I thought using a variable would allow me to edit it in one place in case it ever needs to change. – flyingL123 Jan 15 '20 at 21:11
  • If you think the value of color might change over time then a better approach could be to create a stored procedure that accepts color as a parameter and then executes your query. – Nathan Griffiths Jan 16 '20 at 05:01

1 Answers1

2

As explained in the documentation:

BigQuery views are subject to the following limitations:

  • You cannot reference query parameters in views.

What you want to do is not allowed. A view is limited to a single SELECT statement.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786