Sql View with Cte
Hi experts,
I need to generate a list of all the dates 14 days before current date to 90 days from current date.I have the following cte and I would like to create a view from it which I have to use it in access to link to other tables.
When it is less than 100 and when I exclude option part, I am able to create the view.
But when use option,
create view vw_WaterFallDates as
WITH date_range (calc_date) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date) FROM date_range
WHERE DATEADD(dd, 1, calc_date) < CURRENT_TIMESTAMP + 90)
SELECT calc_date FROM date_range option (maxrecursion 150)
It gives me the following error while creating the view:
Incorrect syntax near the keyword 'option'.
Please let me know how to fix it.
Thanks!
I need to generate a list of all the dates 14 days before current date to 90 days from current date.I have the following cte and I would like to create a view from it which I have to use it in access to link to other tables.
When it is less than 100 and when I exclude option part, I am able to create the view.
But when use option,
create view vw_WaterFallDates as
WITH date_range (calc_date) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date) FROM date_range
WHERE DATEADD(dd, 1, calc_date) < CURRENT_TIMESTAMP + 90)
SELECT calc_date FROM date_range option (maxrecursion 150)
It gives me the following error while creating the view:
Incorrect syntax near the keyword 'option'.
Please let me know how to fix it.
Thanks!
yawkey13
You are missing the comma between "maxrecursion" and "150"
IGNORE THAT. I'm incorrect.
http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/
Use it when querying your view . . . not in the view.
Use it when querying your view . . . not in the view.
ASKER
ged325,
I have to use this view as a data source in access, not sure how to use option in that case.
Please let me know. Thanks!
I have to use this view as a data source in access, not sure how to use option in that case.
Please let me know. Thanks!
First create yoru view:
create view vw_WaterFallDates as
WITH date_range (calc_date) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date) FROM date_range
WHERE DATEADD(dd, 1, calc_date) < CURRENT_TIMESTAMP + 90)
SELECT calc_date FROM date_range
Then create a query:
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
create view vw_WaterFallDates as
WITH date_range (calc_date) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date) FROM date_range
WHERE DATEADD(dd, 1, calc_date) < CURRENT_TIMESTAMP + 90)
SELECT calc_date FROM date_range
Then create a query:
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
ASKER
ged325,
When I create a query
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
in access, I get the error
Synatax error in FROM clause error
Any other way to do it other than temp tables.
Thanks!
When I create a query
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
in access, I get the error
Synatax error in FROM clause error
Any other way to do it other than temp tables.
Thanks!
Don't set the option maxRecursion in your view.
this is your view:
create view vw_WaterFallDates as
WITH date_range (calc_date) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date) FROM date_range
WHERE DATEADD(dd, 1, calc_date) < CURRENT_TIMESTAMP + 90)
SELECT calc_date FROM date_range
And your query in Access is:
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
this is your view:
create view vw_WaterFallDates as
WITH date_range (calc_date) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date) FROM date_range
WHERE DATEADD(dd, 1, calc_date) < CURRENT_TIMESTAMP + 90)
SELECT calc_date FROM date_range
And your query in Access is:
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
ASKER
I did just like above and I get error in access.
Try one more thing:
in SQL:
Create procedure calc_date
as
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
Then see if you can called the stored proc from Access.
in SQL:
Create procedure calc_date
as
SELECT calc_date FROM vw_WaterFallDates option (maxrecursion 150)
Then see if you can called the stored proc from Access.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I wanted a soultion with the view and I figured out one.