Geek Answers Handbook

MS SQL Server - How to create a view from CTE?

with cte as (
    select '2014-03-10 08:00:00' as Dates
    union all
    select '2014-05-11 14:00:00'
)
select * from cte 
join someTable on 1=1 
OPTION (MAXRECURSION 0)

Here above, SQL displays as a charm all the hours between two dates and a field obtained from joining another table:

2014-03-10 02:00:00    A
2014-03-10 02:00:00    B
2014-03-10 03:00:00    A
2014-03-10 03:00:00    B
...
2014-05-11 13:00:00    A
2014-05-11 13:00:00    B
2014-05-11 14:00:00    A
2014-05-11 14:00:00    B

I would like to create a view from this, but I fail. I tried several things, but to no avail. The following is returned:Incorrect syntax near the keyword 'OPTION'.

CREATE VIEW viewName as 
with cte as (
    select '2014-03-10 08:00:00' as Dates
    union all
    select '2014-05-11 14:00:00'
)
select * from cte 
join someTable on 1=1 
OPTION (MAXRECURSION 0)
+4
source share
3 answers

You cannot specify a parameter MAXRECURSIONinside the view.

From http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/ :

To use the MAXRECURSION option, you first need to create your view without using the MAXRECURSION option:

USE AdventureWorks;
GO
CREATE VIEW vwCTE AS
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  HumanResources.Employee AS e
        ON cte.ManagerID = e.EmployeeID
)
-- Notice the MAXRECURSION option is removed
SELECT EmployeeID, ManagerID, Title
FROM cte
GO

, , MAXRECURSION:

USE AdventureWorks;
GO
SELECT  EmployeeID, ManagerID, Title
FROM    vwCTE
OPTION (MAXRECURSION 2);

. AaskashM fooobar.com/questions/16346/...

+4

, ,

CREATE VIEW viewName AS 
    WITH TenNumbers AS (
        SELECT Number
        FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS T(Number)
    )
    ,Numbers AS (
        SELECT ROW_NUMBER() OVER (ORDER BY T10.Number) AS Number
        FROM TenNumbers AS T10
             CROSS JOIN TenNumbers AS T100
             CROSS JOIN TenNumbers AS T1000
             -- ...
    )
    SELECT DATEADD(hour, Number - 1, '20140310 08:00:00') AS Dates
    FROM Numbers
0

100 OPTION VIEW, CTE, OPTION, OPENQUERY VIEW.

, , :

USE AdventureWorks;
GO

CREATE VIEW vwCTE AS
select * from OPENQUERY([YourDatabaseServer], '
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
  SELECT EmployeeID, ManagerID, Title
    FROM AdventureWorks.HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
  SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  AdventureWorks.HumanResources.Employee AS e
      ON cte.ManagerID = e.EmployeeID
)
-- Notice the MAXRECURSION option is removed
SELECT EmployeeID, ManagerID, Title
  FROM cte
  OPTION (MAXRECURSION 0)
' ) x
GO

, , .. (, , sproc ).

, , OPTION.

0

More articles:


All Articles