BigQuery SQL syntax error: Expected "(" or "," or keyword SELECT but got end of script at [9:2]

Question

I am new and not sure what I am doing wrong I am trying to create a temp file and I am getting a syntax error with the following query:

WITH trees_temp AS
(
    SELECT *   
    FROM
        (SELECT *
         FROM bigquery-public-data.new_york_trees.tree_census_1995
        JOIN bigquery-public-data.new_york_trees.tree_census_2005
             ON tree_census_1995.diameter = tree_census_2005.tree_dbh
        WHERE diameter > 30)
)

oh sorry Im learning I thought the SELECT after the WITH is what it needed? Sorry — Brandy
– Brandy, Commented Apr 14, 2021 at 20:01
. . The SELECT is used to define the CTE. Then there should be another SELECT that uses the CTE. — Gordon Linoff
– Gordon Linoff, Commented Apr 14, 2021 at 20:33

Leigha Jarett · Accepted Answer · 2021-04-14 22:04:16Z

1

You're using a common table expression - meaning that temporary table (trees_temp) is only available in the query you're running. You'll need to follow your WITH statement with a SELECT statement to actually query it.

WITH trees_temp AS
(
 SELECT *
 FROM bigquery-public-data.new_york_trees.tree_census_1995
 JOIN bigquery-public-data.new_york_trees.tree_census_2005
    ON tree_census_1995.diameter = tree_census_2005.tree_dbh
 WHERE diameter > 30
)
SELECT * FROM trees_temp;

If you want to create a temporary table that can be used across different queries you can create a new table or a view and give it an expiration time. For example:

CREATE OR REPLACE TABLE my-dataset.trees_temp
OPTIONS(
  expiration_timestamp=TIMESTAMP "2021-04-15 00:00:00 UTC"
)
as 
(
 SELECT t95.*
 FROM bigquery-public-data.new_york_trees.tree_census_1995 as t95
 JOIN bigquery-public-data.new_york_trees.tree_census_2005 as t05
    ON t95.diameter = t05.tree_dbh
 WHERE diameter > 30
)

answered Apr 14, 2021 at 22:04

Leigha Jarett

1886 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brandy Over a year ago

thank you so much Leigha and for the link!! that helped so much!

Collectives™ on Stack Overflow

BigQuery SQL syntax error: Expected "(" or "," or keyword SELECT but got end of script at [9:2]

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related