Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql View with Cte

Posted on 2012-04-05
11
Medium Priority
?
417 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
  • 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 41

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 41

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 41

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 41

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline

824 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