Incorrect syntax near the keyword 'with'

Question

1.00/5 (1 vote)

See more:

, +

Hello Guys,

I'm trying to create a CTE, but facing this issue while creating. Can anyone please guide me on this.

What I have tried:

SQL

;WITH myCTE(fname , lname) 
as(
select fname,lname from myTable
)
select * from myCTE

Posted 11-Oct-19 4:39am

srko

Updated 16-Oct-19 3:19am

v3

Add a Solution

Comments

ZurdoDev 16-Oct-19 10:00am

I don't get the error, which tells me you haven't posted the full sql that is giving you the error.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2019-10-11T04:46:00

Solution 1

Try using the same name in both places:

SQL

WITH myCTE(fname , lname) 
AS(
SELECT fname,lname FROM myTable
)
SELECT * FROM myCTE

Posted 11-Oct-19 4:46am

OriginalGriff

Comments

srko 16-Oct-19 9:18am

@OriginalGriff tried the same thing but is not working, will this be due to SSMMS support?

ZurdoDev 16-Oct-19 10:01am

Try using the same name in both places:
I must be blind. The only difference from what OP posted and what you posted is you omitted the semi-colon.

[no name] 16-Oct-19 10:03am

Check the versions of the question :)

ZurdoDev 16-Oct-19 10:08am

I thought about that but was too lazy. However, that still won't give the error the OP posted. But it would fix the original problem.

srko 16-Oct-19 10:22am

Yes guys, i have updated the question.
Removed ;
changed the name of the CTE.

tried the both, but is not working

[no name] 16-Oct-19 10:34am

What sql server version are you using?

srko 16-Oct-19 11:15am

I'm using 2016

[no name] 16-Oct-19 11:48am

is the complete error message something like this?

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ''.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

?

srko 16-Oct-19 11:50am

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.

[no name] 16-Oct-19 12:03pm

Sorry, I'm out of ideas. I suggest to retype the whole statement again (and no copy/paste), maybe some invisible characters do interfere .... e.g. Alt&240 is such a beast

ZurdoDev 17-Oct-19 8:50am

Like I said before, then you have more SQL you aren't showing us or you have a weird special character that you don't notice.

If I type in what you have posted it works. Which means you are doing something different.

srko 17-Oct-19 10:10am

tried with new query window, new CTE script but still it's not working

ZurdoDev 17-Oct-19 10:14am

Perhaps provide a screenshot of what you are doing.

srko 17-Oct-19 10:51am

dont know how to add screenshot here :(

ZurdoDev 17-Oct-19 11:05am

Click your username in the upper right, then go to My Uploads on the left and then upload an image and then paste the url in here.