Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

CTE in a View

Jul 11 2009 11:53AM by Madhivanan   

It is possible to use Common Table Expression in a View. But newbies wonder why the following is not possible

create view numbers
as
with numbers(n) as
(
select 1 as n union all select n+1 from numbers where n<10000
)

select n from numbers option(maxrecursion 0)

Which when created results to the error

Msg 156, Level 15, State 1, Procedure numbers, Line 15 Incorrect syntax near the keyword 'option'.

The correct way of doing it is to create a view without option(maxrecursion 0) and use it when querying a view

create view numbers 
as 
with numbers(n) as 
( 
select 1 as n union all select n+1 from numbers where n<10000 
) 

select n from numbers 
GO

select * from numbers option (maxrecursion 0)

Tags: t-sql, sql_server, view, cte,


Madhivanan
3 · 40% · 12936
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • This works, but if I then try to create a View using select * from numbers option (maxrecursion 500) I get an error message "Query hints cannot be used in this query type"

    Any ideas how to use suggestion in a view?

    Thanks

    commented on Aug 31 2011 10:21AM
    pbaxter
    2764 · 0% · 4
  • pbaxter, which version of SQL Server are you using? The above code works from version 2005 onwards

    commented on Sep 2 2011 2:47AM
    Madhivanan
    3 · 40% · 12936
  • I think I know the problem. My query is written in SQL Server 2005, but refers to a linked server that is not SQL Server (rather PostGreSQL). I don't think the maxrecursion value is recognized by this server (or by the ODBC connector we use).

    commented on Sep 8 2011 10:08AM
    pbaxter
    2764 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]