[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sql View with Cte

Posted on 2012-04-05
11
Medium Priority
?
421 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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