View and Common Table Expressions (CTE)

SQL Server 2008 R2
Vitor Montalvão at EE showed me how to solve a problem with a CTE.  So I take the CTE SQL (see Correct SQL below) and put into a view -->vw_aa_CTE_Events

All works well

However if I open the view in design view - part of the SQL is missing (see Problem SQL below) and will not run.

But again if I try to run it with  --> select * from vw_aa_CTE_Events <-- No Problems.
Any ideas?

Thanks for any help.

------------ Correct SQL --------------
WITH CTE_Events (RowNumber, EventID, StartDate)
AS (
      SELECT ROW_NUMBER() OVER (ORDER BY Evt_StartDate DESC), EventID, Evt_StartDate
      FROM tbl_EventNames)
SELECT E1.EventID, E1.StartDate, ISNULL(E2.StartDate,DateAdd(yy,5,GETDATE())) AS StopDate
FROM CTE_Events E1
      LEFT JOIN CTE_Events E2 ON E1.RowNumber = E2.RowNumber+1

------------ Problem SQL -----------------
SELECT     ROW_NUMBER() OVER (ORDER BY Evt_StartDate DESC), EventID, Evt_StartDate
FROM         tbl_EventNames)
    SELECT     E1.EventID, E1.StartDate, ISNULL(E2.StartDate, DateAdd(yy, 5, GETDATE())) AS StopDate
     FROM         CTE_Events E1 LEFT JOIN
                            CTE_Events E2 ON E1.RowNumber = E2.RowNumber + 1
LVL 2
LJGAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The problem is with the OVER function. Isn't a bug but a "feature". Maybe in later versions of SSMS it work properly.
0
 
ste5anSenior DeveloperCommented:
Don't use the design view...
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
You can put it in a proc like:
Create Proc spMyProc
As
Begin
WITH CTE_Events (RowNumber, EventID, StartDate)
AS (
      SELECT ROW_NUMBER() OVER (ORDER BY Evt_StartDate DESC), EventID, Evt_StartDate
      FROM tbl_EventNames)
SELECT E1.EventID, E1.StartDate, ISNULL(E2.StartDate,DateAdd(yy,5,GETDATE())) AS StopDate
FROM CTE_Events E1
      LEFT JOIN CTE_Events E2 ON E1.RowNumber = E2.RowNumber+1

End

Open in new window


Now use:

Exceute spMyProc
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Strange. I don't have any problem with opening a view with CTE in design view.
DesignView.PNG
You may be facing some bug in SSMS. Can you check which version are you running (menu Help/About)?
0
 
LJGAuthor Commented:
Thanks Vitor
I'm using SQL Server 2008 R2 with Microsoft SQL Server Management Studio -->      10.50.4033.0

Just for info, when I try to go to design view on the View I get the following message.
"SQL text cannot be represented in the grid pane and diagram pane."
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I made some modifications on my view. Is the ROW_NUMBER() that can't be represented in design view.
0
 
Scott PletcherSenior DBACommented:
You can get rid of the CTE and use derived tables / subqueries instead:


SELECT E1.EventID, E1.StartDate, ISNULL(E2.StartDate,DateAdd(yy,5,GETDATE())) AS StopDate
FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY Evt_StartDate DESC), EventID, Evt_StartDate
      FROM tbl_EventNames
) AS E1
LEFT OUTER JOIN (
      SELECT ROW_NUMBER() OVER (ORDER BY Evt_StartDate DESC), EventID, Evt_StartDate
      FROM tbl_EventNames
) AS E2 ON E1.RowNumber = E2.RowNumber+1
0
 
LJGAuthor Commented:
Thanks everyone for the help!
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
create table #t (EventID int, StartDate date);
insert into #t(EventID , StartDate) values 
(1,'20150101'),
(2,'20150115'),
(3,'20150201'),
(4,'20150215'),
(5,'20150301'),
(6,'20150315'),
(7,'20150330')

select * from #t

Open in new window


SELECT E1.EventID, E1.StartDate, (
      SELECT Top (1) StartDate
      FROM #t E2
        Where E1.EventID < E2.EventID
) AS StopDate
FROM #t E1

Produces:
1	2015-01-01	2015-01-15
2	2015-01-15	2015-02-01
3	2015-02-01	2015-02-15
4	2015-02-15	2015-03-01
5	2015-03-01	2015-03-15
6	2015-03-15	2015-03-30
7	2015-03-30	NULL

Open in new window

0
 
LJGAuthor Commented:
eghtebas - Thanks for the other solution - I like both

I've given the point to Vitor Montalvão as I want him to have all the points I've given.  

Is there a way to give you some points in addition?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
LJG,

Thank you for the though. It is okay as it is.

But to answer you question, yes there is a way I think it start with get attention button somewhere in the thread. But it is not necessary for this question.

Also, there is maximum of 500 points per question.

Glad you like the solution.

Mike
0
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.

All Courses

From novice to tech pro — start learning today.