CTE Issue

Question

Post reply

CTE Issue

bwagner 90365

SSC Veteran

Points: 265
More actions
April 14, 2010 at 4:25 pm

#217874

I'm trying to create a query that utilizes a CTE. Here is what I have so far:
use cu_hds;
With DialerResults AS
(Select DateTime,
CampaignID,
CallResult,
Phone,
AccountNumber,
FirstName,
LastName
from t_Dialer_Detail
)
Select *
from DialerResults
When I run this, I get the following error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'With'.
Not sure where to go from here. I have used CTE's before, however not on this server. Any advice would be greatly appreciated. Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Edit: quote removed...

Did you try to put the semicolon right in front of the WITH keyword?

I can't see anything wrong at all with the query as it is posted. What happens if you add a GO right in front of the CTE?

Just guessing though...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

In your use cu_hds statement, follow it up with a GO

Then you can add the semi-colon, then run your CTE.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

bwagner 90365 SSC Veteran Points: 265 More actions · Answer 3

Here are a few versions of the query I've already attempted. I'll only be posting the first few lines as it appears that's the section in question.

use cu_hds

;With DialerResults AS

(Select DateTime,

Returns:

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'With'.

----------------

use cu_hds;

GO

With DialerResults AS

(Select DateTime,

Returns:

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'With'.

-----------------

use cu_hds

GO;

With DialerResults AS

(Select DateTime,

Returns:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'GO'.

------------------

use cu_hds

GO

;With DialerResults AS

(Select DateTime,

Returns:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near ';'.

------------------

Is it possible that the SQL server I'm on doesn't allow the use of CTEs? I'm very confused at this point...

LutzM SSC Guru Points: 107049 More actions · Answer 4

What's the result of SELECT @@VERSION ?

If it's less than something like "Microsoft SQL Server 2005 - 9. ..."

then you're absolutely right. CTE's are not supported below SS2K5.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

In addition to the version, try executing the commands in separate batches (or eliminate the use db statement altogether).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

bwagner 90365 SSC Veteran Points: 265 More actions · Answer 6

Therein lies my problem. Nothing like posting a question before doing your homework. Sorry for the trouble.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 7

That makes plenty of sense.

Thanks for posting back.

CTEs cannot be used (as Lutz said) prior to SQL 2005 (new feature).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events