Can anyone tell me how I should correct my query?-CodePudding

I keep getting an error message for the query below. I am not sure what the problem may be, so any help is appreciated. Also this is the error message I keep getting. Thanks

Syntax error: Expected keyword AS but got "(" at [1:15]

WITH PopvsVac (continent, location, date, population, new_vaccinations, RollingPeopleVaccinated) AS 
    (SELECT dea.continent,
         dea.location,
         dea.date,
         dea.population,
         vac.new_vaccinations,
         SUM(CONVERT(int, vac.new_vaccinations)) OVER (PARTITION BY dea.location
    ORDER BY  dea.location,dea.date) AS RollingPeopleVaccinated
    FROM `phonic-scheme-348721.covid_deaths.deaths` dea
    JOIN `phonic-scheme-348721.covid_vaccine.vaccine` vac
        ON dea.location = vac.location
            AND dea.date = vac.date
    WHERE dea.continent is NOT NULL )
SELECT *
FROM PopvsVac

CodePudding user response：

Usage of WITH clause: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause

Sample: with test as (select 1 as col1) select * from test

As jaytiger mentioned in comment, you have extra things added into your WITH clause. I can not run your query, but just removing those extra things should work.

CodePudding user response：

Please try below.

WITH
PopvsVac AS (
    SELECT
        dea.continent,
        dea.location,
        dea.date,
        dea.population,
        vac.new_vaccinations,
        SUM(CONVERT(int, vac.new_vaccinations)) OVER (
            PARTITION BY dea.location ORDER BY  dea.location,dea.date
        ) AS RollingPeopleVaccinated
    FROM `phonic-scheme-348721.covid_deaths.deaths` dea
    JOIN `phonic-scheme-348721.covid_vaccine.vaccine` vac
        ON dea.location = vac.location AND dea.date = vac.date
    WHERE dea.continent is NOT NULL
)
SELECT *
FROM PopvsVac
;

Syntax

query_expr: [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ] { select | ( query_expr ) | set_operation } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ]

Please check the below references for more information: [1] Syntax [2] Standard SQL