Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
osupratt
Posting Yak Master
238 Posts |
Posted - 2010-02-02 : 20:35:16
|
Can the MAXRECURSION be used in a View? The following CTE runs fine, but when I try to Create a View using it, it says syntax error near word 'option'USE [Shale_Ticket]GO/****** Object: View [dbo].[VW_Shale_EOGDiscLeaseDt] Script Date: 05/18/2009 13:28:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[VW_Shale_EOGDiscLeaseDt]ASWith Date_CTE (Date) AS(SELECT DATEADD(DAY,DATEDIFF(DAY,0,'9/12/2009'),0)UNION ALLSELECT DATEADD(d,1,Date)FROM Date_CTEWHERE DATEADD(d,1,Date)<DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+1,0))SELECT Date FROM Date_CTEOPTION (MAXRECURSION 32000) |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2010-02-03 : 10:20:09
|
this is what i revised to, but when selecting the view and 'running' the view i get the "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." error.USE [Shale_Ticket]GO/****** Object: View [dbo].[VW_SHALE_EOG_DiscDate] Script Date: 05/18/2009 13:28:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[VW_SHALE_EOG_DiscDate]ASWith [VW_SHALE_EOG_DiscDate](Date) AS(SELECT DATEADD(DAY,DATEDIFF(DAY,0,'9/12/2009'),0)UNION ALLSELECT DATEADD(d,1,Date)FROM [VW_SHALE_EOG_DiscDate]WHERE DATEADD(d,1,Date)<DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+1,0))SELECT Date FROM [VW_SHALE_EOG_DiscDate] GOSELECT * FROM [VW_SHALE_EOG_DiscDate] OPTION (MAXRECURSION 32000)is there a way to build this query (all dates from 9/1/2010 to GETDATE()) other than with a CTE? thanks for your help. |
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2010-02-03 : 10:46:49
|
Nevermind, something as simple as this did the trick:USE [Shale_Ticket]GO/****** Object: View [dbo].[VW_SHALE_EOG_DiscDate] Script Date: 05/18/2009 13:28:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[VW_SHALE_EOG_DiscDate]ASSELECT DATEADD(d,number,'9/11/2009')AS DateFROM master..spt_valuesWHERE number BETWEEN 1 ANDDATEDIFF(d,'9/11/2009',GETDATE())AND type = 'P' |
|
|
|
|
|
|
|