14

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 :(

25

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
| improve this answer | |
  • The trick for me was to remove some extra parentheses. create view myView as ( select ... ) works and create view myView as with tempTbl as select ... works but create view myView as ( with tempTbl as select ... ) is a syntax error. ` – Molomby Apr 13 '16 at 7:02
6
CREATE VIEW 
someview
AS
WITH CTEstuff AS (SELECT etc)
SELECT * FROM CTESTuff;
| 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 says try topanswers.xyz Oct 26 '11 at 6:39
  • Btw postgres is awesome! (speaking as an Oracle DBA) I'm sure you agree :) – Jack says try topanswers.xyz 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 says try topanswers.xyz Oct 27 '11 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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