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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.