February 11, 2010 at 9:01 am
The original code was from yesterday's question of day (Feb 10), according to today's answer, it did not work in SQL 2005, but I did not make it work in 2008 either, It was ok to run CTE part only, but not with the view, any hints? thanks a lot!
create view month_since_200701
as
WITH mycte AS (SELECT cast ('2007-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue + 1 < getdate())SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth from mycte OPTION ( MAXRECURSION 0 )
February 11, 2010 at 9:56 am
I can't get it to work with the maxrecursion option in the view definition. Works if I query the view with that hint, but not in the view definition itself.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2010 at 8:40 pm
According to MSDN:
http://msdn.microsoft.com/en-us/library/ms187956.aspx
This is the 2008 article on creating views. It states that the select statement cannot contain the into keyword nor can it contain the option clause.
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply