Solved

Sql View with Cte

Posted on 2012-04-05
11
409 Views
Last Modified: 2012-04-21
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!
0
Comment
Question by:MPI_IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 6

Expert Comment

by:yawkey13
ID: 37813718
You are missing the comma between "maxrecursion" and "150"
0
 
LVL 6

Expert Comment

by:yawkey13
ID: 37813722
IGNORE THAT.  I'm incorrect.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37813908
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:MPI_IT
ID: 37813943
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37813958
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
 

Author Comment

by:MPI_IT
ID: 37823280
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37827754
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
 

Author Comment

by:MPI_IT
ID: 37828203
I did just like above and I get error in access.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37829746
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
 

Accepted Solution

by:
MPI_IT earned 0 total points
ID: 37851860
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
 

Author Closing Comment

by:MPI_IT
ID: 37875053
I wanted a soultion with the view and I figured out one.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question