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!
MPI_ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yawkey13Commented:
You are missing the comma between "maxrecursion" and "150"
0
yawkey13Commented:
IGNORE THAT.  I'm incorrect.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

MPI_ITAuthor Commented:
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!
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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)
0
MPI_ITAuthor Commented:
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!
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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)
0
MPI_ITAuthor Commented:
I did just like above and I get error in access.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
MPI_ITAuthor Commented:
I figured out a solution with a view itself.I used the following :

WITH date_range1 (calc_date1) AS (SELECT DATEADD(dd, DATEDIFF(dd, 14, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date1) FROM date_range1        
WHERE DATEADD(dd, 1, calc_date1) < CURRENT_TIMESTAMP),
date_range2 (calc_date2) AS (SELECT DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)
UNION ALL SELECT DATEADD(dd, 1, calc_date2) FROM date_range2        
WHERE DATEADD(dd, 1, calc_date2) < CURRENT_TIMESTAMP + 90)
select calc_date1 dates from date_range1 union
select calc_date2 dates from date_range2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MPI_ITAuthor Commented:
I wanted a soultion with the view and I figured out one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.