Create View with option (maxrecursion)

Question

I want to create a view in SQL as tableau software do not support CTE function. I'm Unable to add a view as I am using a MAXRECURSION. The error message is

Incorrect syntax near the keyword 'OPTION'.

Below is my existing CTE query using recursive.

Where and what do I need to add in my existing query?

WITH shiftHours AS (
   -- This is a recursive CTE, code removed to improve readability
)

SELECT *
FROM (
  SELECT * from shiftHours
) AS t
    PIVOT (
SUM(hourValue)
FOR hourOrdinal IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
) AS pvt
OPTION (MAXRECURSION 0)
GO

Answer 1

While you can't create a VIEW that explicitly contains the OPTION clause, if you have a CTE that's expected to return more than 100 expected results, and want to avoid having to add the OPTION statement to your VIEW calls, try executing the CTE - including the OPTION clause - in an OPENQUERY statement within your VIEW.

In your example, it would probably look something like this:

select * from OPENQUERY([YourDatabaseServer], '
WITH shiftHours AS (
     -- This is a recursive CTE, code removed to improve readability
)

SELECT *
  FROM (
    SELECT * from YourDatabase.YourUser.shiftHours
  ) AS t
  PIVOT (
    SUM(hourValue)
      FOR hourOrdinal IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
  ) AS pvt
  OPTION (MAXRECURSION 0)

') x

Notice that you must fully qualify object references, i.e. the database and user specifications must prefix the object (table, view, sproc, or function) references.

Sure, it's a little ugly, but gets the job done nicely, and avoids having to add that pesky OPTION clause.