CODEWITHSUNDEEP
sqlgoogle-bigquery
disruptive
disruptive

Reputation: 5946

WITH statement followed by SET in BigQuery

I'm pipelining a few SQL statements together and finding that I am having trouble following on from a WITH statement and using the results to the then go on and SET a variable. The error I'm getting is:

Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword SET at [17:2]

The statements look like

WITH newtable AS (SELECT session, var FROM `table` WHERE session...)
(SET myvariable = (SELECT count(*) FROM newtable WHERE ....);

Upvotes: 0

Views: 818

Answers (1)

Anjela B
Anjela B

Reputation: 1201

You can only use WITH following a SELECT statement as per this GCP Documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#sql_syntax

Given that limitation, you can just use DDL statement to create a table from a result of a query and set the variable.

create table newtable AS (SELECT session, var FROM table);

DECLARE myvariable NUMERIC;
SET myvariable = (SELECT count(*) FROM newtable);

Upvotes: 1

Related Questions