Solved

Sql View with Cte

Posted on 2012-04-05
11
397 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 39

Expert Comment

by:Kyle Abrahams
ID: 37813908
0
 

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 39

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 39

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 39

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now