CTE within a CREATE VIEW (common table expression)

[ejsmith]

All of the documentation on Common Table Expressions specifically mentions that you can create and reference a CTE within a CREATE VIEW statment. Trouble is that I can't find an example of this and I've run out of things to try.

Has anyone used a CTE in a view? Any suggestions on the correct syntax? Any advice or exmples would be greatly appreciated.

Here is my code snippet that isn't working...
GO
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'vw_MyView'
AND type = 'V')
DROP VIEW vw_MyView
GO
CREATE VIEW vw_MyView
AS
WITH cte_MyInfo(Field1, Field2, Field3)
AS (SELECT Field1, Field2, Field3
FROM vw_Member_NY_SCAs_Completed)
SELECT
SCABase.ManagedPersonID
,Assessment_Desc
...etc

Thanks in advance!

 

[AlexCuse]

This works fine for me. You are using SQL Server 2005, right? If so, can you post your whole code?


[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[ejsmith]

My apologies - I have access to several servers and I just checked the version... the one I was trying to run this on is still on 2000.

Thanks anyway!