I’ve been trying to build up a Temporary Table with both CREATE TEMPORARY TABLE and WITH statements in the following query. So far I couldn’t get any positive result and I don’t know what’s going on. In last line, the error alert says: “Syntax error: Expected “(” or “,” or keyword SELECT but got end of script at [28:62]”
x
WITH analysis_fall_2021_season AS(SELECTstart_station_name,end_station_name,EXTRACT (DATE FROM started_at) AS start_date,EXTRACT (DATE FROM ended_at) AS end_date, EXTRACT (TIME FROM started_at) AS start_time,EXTRACT (TIME FROM ended_at) AS end_time,DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,member_casualFROM (SELECTfall_analysis.ride_id, fall_analysis.started_at, fall_analysis.ended_at, fall_analysis.start_station_name, fall_analysis.end_station_name, fall_analysis.member_casualFROM `ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysisINNER JOIN `ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_202011ON fall_analysis.member_casual = fall_202011.member_casualINNER JOIN `ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_202012ON fall_analysis.member_casual = fall_202012.member_casual))Advertisement
Answer
As suggested by @Paul, You get this error because a CTE (i.e. the WITH statement) is only part of a query. It needs to be followed by another statement, usually a SELECT. You can also refer to this StackOverflow question where the same error has been discussed.
You can try the below code which I have modified accordingly to resolve the error.
WITH analysis_fall_2021_season AS(SELECTstart_station_name,end_station_name,EXTRACT (DATE FROM started_at) AS start_date,EXTRACT (DATE FROM ended_at) AS end_date,EXTRACT (TIME FROM started_at) AS start_time,EXTRACT (TIME FROM ended_at) AS end_time,DATETIME_DIFF (ended_at,started_at, MINUTE) AS total_lenght,member_casualFROM(SELECTfall_analysis.ride_id,fall_analysis.started_at,fall_analysis.ended_at,fall_analysis.start_station_name,fall_analysis.end_station_name,fall_analysis.member_casualFROM`ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysisINNER JOIN`ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_202011ONfall_analysis.member_casual = fall_202011.member_casualINNER JOIN`ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_202012ONfall_analysis.member_casual = fall_202012.member_casual))SELECT * FROM analysis_fall_2021_season;