Hi all,
Thanks for taking a look at the question!
I've got a View which contains a recursive CTE to generate dates between a given start and end date.
WITH D AS (
SELECT CAST(DATEADD(month, DATEDIFF(month, 0, DATEADD(m, 0 - 12, GETUTCDATE())), 0) AS DATETIME) as DayDate
UNION ALL
SELECT DATEADD(day, 1, DayDate)
FROM D
WHERE DayDate < CAST(DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0) AS DATETIME)
)
There's some other SQL code that joins onto that CTE.
Usually when executing this query I'd include
OPTION (MAXRECURSION 0)
at the end of the query, if you don't SQL Server will error.
I'm porting this query to Power BI but can't find a way to constraint the MAXRECURSION option like I would in a standard SQL statement.
I can't include this in the View because it'll error (just like an OrderBy)
I can't see a way to edit the query once I've created a SQL Server Data Set querying the View
When I navigate though 'Get Data' -> SQL Server -> Advanced Options (DirectQuery ticked) then enter a query such as
SELECT * FROM MyView OPTION(MAXRECURSION 0)
The query executes and you see a result set, but then when I click Load to create the data set I get an error stating: "Microsoft SQL: Incorrect syntax near the keyword 'OPTION'."
Any ideas as to what my options are here, and if there's a best practice for this kind of query?
Thanks in advance & kind regards,
Rob
