Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to create VIEW using WITH clauses, but really can't find any references on correct syntax.

I want smth like this

WITH TempTbl AS (SELECT ...)
CREATE VIEW SomeView
SELECT *
FROM TempTbl

And what is the correct syntax for using several WITH clauses?

Nothing useful on MSDN :(

share|improve this question

2 Answers 2

up vote 8 down vote accepted

The CTE goes inside the view.

Take a query with a CTE

WITH cte AS (...) SELECT ...;

Just add CREATE VIEW AS .. GO

CREATE VIEW
AS
WITH cte AS (...) SELECT ...;
GO

MSDN does describe multiple CTEs (See example j)

CREATE VIEW
AS
WITH
   cte1 AS (...),
   cte2 AS (...),
   cte3 AS (...)
SELECT ...
GO
share|improve this answer
    
Thanks, gbn! I should have read it more careful! –  bazzilic Oct 26 '11 at 5:54
CREATE VIEW 
someview
AS
WITH CTEstuff AS (SELECT etc)
SELECT * FROM CTESTuff;
share|improve this answer
    
welcome to dba.se and thanks for the contribution. Just so you know, the question here is really too basic for the site and will probably be closed - but that is no reflection on your answer :) Given your skills on SQL Server and postgres there will be plenty more on-topic questions right up your street, so I hope you stick around. –  Jack Douglas Oct 26 '11 at 6:39
    
Btw postgres is awesome! (speaking as an Oracle DBA) I'm sure you agree :) –  Jack Douglas Oct 26 '11 at 6:40
1  
Nice to be here. I think it'd be a shame to consider any question too basic; although I agree it might not be an administration question and might belong in a different forum, perhaps stack exchange itself. Anyhoo thanks! –  Scott Herbert Oct 26 '11 at 23:08
    
I think you are right SO is probably best, but we try and be a bit flexible especially if people have already put effort into answering by the time we notice :) –  Jack Douglas Oct 27 '11 at 3:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.