12

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
James Z
  • 12,209
  • 10
  • 24
  • 44
Vannessa
  • 173
  • 1
  • 3
  • 10
  • 1
    What is happening with yours? Error? – Yatrix May 06 '16 at 03:51
  • My current query is using CTE, so I am unsure on how to add a view so that I can put it in Tableau! Do you have any idea how to add view? @Yatrix – Vannessa May 06 '16 at 03:54
  • Unfortunately, I believe the `OPTION` has to be applied on the outermost query (the one that *uses* the view) and if the software you're working with doesn't like CTEs, we can probably guess that it may have little truck with query hints. – Damien_The_Unbeliever May 06 '16 at 07:16
  • So am I able to add a view with `OPTION`? If yes, how do I do it? @Damien_The_Unbeliever – Vannessa May 06 '16 at 07:26
  • 4
    What I'm saying is that I believe you would have to create the view *without* specifying `OPTION` but then, when you write a query that *references* the view, you would add the `OPTION` to *that* query. – Damien_The_Unbeliever May 06 '16 at 07:28
  • but I need the `OPTION`, otherwise theres this output _"The statement terminated. The maximum recursion 100 has been exhausted before statement completion"._ @Damien_The_Unbeliever – Vannessa May 06 '16 at 07:43
  • Could you put the result into a table and then access that from Tableau? – RichardCL May 06 '16 at 08:22
  • @Damien_The_Unbeliever is right. See Books Online > CREATE VIEW (Transact-SQL): https://msdn.microsoft.com/en-GB/library/ms187956.aspx > Arguments > select_statement. The SELECT clauses in a view definition cannot include the OPTION clause and several other things. – RichardCL May 06 '16 at 08:26
  • 1
    I suggest you to put your cte into a function and then create view based on function. – gofr1 May 06 '16 at 09:54
  • Possible duplicate of [MS SQL Server - How to create a view from a CTE?](http://stackoverflow.com/questions/27172801/ms-sql-server-how-to-create-a-view-from-a-cte) – James Z May 14 '16 at 14:25

3 Answers3

3

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.

jskipb
  • 71
  • 5
1

OPTION can be used only once per query. You have to use OPTION in query which uses your view.

xdd
  • 535
  • 2
  • 4
-7
Create View [dbo].[YourViewname] as
//Your CTE
go

There should not be any challenges in this. Are you facing any?

Akanksha Singh
  • 351
  • 2
  • 9