Common Table Expression inside View

Question

Post reply

Common Table Expression inside View

jjbear11

Default port

Points: 1497
More actions
December 14, 2009 at 10:29 am

#217576

I'm trying to create a view for my large query which contains 3 common table expressions. Below is an excerpt of what I'm trying to accomplish
create view vDimBooks as (
with Publisher as (
...
),
Author as (
...
),
Editor as (
...)
select * from Publisher
INNER JOIN Author on Publisher.ID = Author.PublisherID
INNER JOIN Editor on Editor.ID = Author.EditorID
)
However, when I try to execute this statement to create the view, I get the following error:
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.
I understand that I need to provide a semi-colon within that whole mix, but am not sure exactly where to place it. I've tried multiple places, but still get the same error. Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

before the with: ";with Publisher"

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jjbear11 Default port Points: 1497 More actions · Answer 2

I have tried to put the semi-colon before the with Publishers, but I get an error which states "Incorrect Syntax near ';'

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Loose the first per of parentheses that surround the whole view definition (the one that starts after vDimBooks and ends the view definition. If this won’t fix it, pleas show the full view definition.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jjbear11 Default port Points: 1497 More actions · Answer 4

jjbear11

Default port

Points: 1497

December 14, 2009 at 10:44 am

#1092094

That was the trick! Thanks Adi!