Creating Views with CTEs - "Incorrect Syntax Near Go" Error

Question

From this answer here I understand that using GO after CTE creation and before CREATE VIEW should in theory deal with the error of:

CREATE VIEW MUST BE THE ONLY STATEMENT IN THE BATCH

However I am receiving an Incorrect Syntax Near GO error now and im not sure for what reason.

My code is simplified below but all ;() punctuation is how I have it

with RESULT1 as (
                code here
                ),
with RESULT2 as (
                code here
                ),
with RESULT3 as (
                code here
                ),
with RESULT4 as (
                code here
                )

GO

create view TestingView as 
            select RESULT4.*
            from RESULT4

Tag your question with the database you are using. – Gordon Linoff Jan 30 '20 at 12:52 — Gordon Linoff, Jan 30 '20 at 12:52
You have to define WITH inside the CREATE VIEW. – jarlh Jan 30 '20 at 12:53 — jarlh, Jan 30 '20 at 12:53

score 3 · Accepted Answer · answered Jan 30 '20 at 12:53

The syntax for multiple CTEs is:

with RESULT1 as (
                code here
                ),
     RESULT2 as (
                code here
                ),
     RESULT3 as (
                code here
                ),
     RESULT4 as (
                code here
                )
SELECT . . .
FROM . . .;

There is no GO. CTEs are not temporary tables.

If you want to create a view, it goes before the WITH. Or, another way to say that, the CTEs are connected to the SELECT, not the CREATE VIEW.

score 0 · Answer 2 · answered Jan 30 '20 at 13:06

Thanks for the replies, the issue made sense for me and i solved it using jarlh pointing out that the WITH should be defined within the CREATE VIEW

So i went from this:

with RESULT1 as (
                code here
                ),
with RESULT2 as (
                code here
                ),
with RESULT3 as (
                code here
                ),
with RESULT4 as (
                code here
                )

GO

create view TestingView as 
            select RESULT4.*
            from RESULT4

To this (which works):

create view TESTVIEW as

with RESULT1 as (
                code here
                ),
with RESULT2 as (
                code here
                ),
with RESULT3 as (
                code here
                ),
with RESULT4 as (
                code here
                )

select RESULT4.*
from RESULT4

Creating Views with CTEs - "Incorrect Syntax Near Go" Error

2 Answers2