April 21, 2010 at 1:01 am
[font="Verdana"]Dear Author,
if you consider the following ... it would be syntax error
CREATE VIEW A_TEMP AS
WITH RESULTSET AS (SELECT * FROM SYS.ALL_OBJECTS WHERE TYPE = 'U')
and if you consider the following
CREATE VIEW A_TEMP AS
WITH RESULTSET AS (SELECT * FROM SYS.ALL_OBJECTS WHERE TYPE = 'U')
SELECT * FROM RESULTSET
it means good you have done.
I conceived your quest statement as 'The Single Defining Select Statement of View Definition' as shown in code segment 1 above.
And i consider the 2nd code segment "SELECT * FROM RESULTSET" as 2nd select statment with in the view defintion.
So please let try to elaborate your quest further so ppl can get really enjoy these quests.
Thank you for next time elaboration.
[/font]
April 21, 2010 at 2:16 am
Good, clean question, Ron. Nice work!
abrar.ahmad-1058946 (4/21/2010)
I conceived your quest statement as 'The Single Defining Select Statement of View Definition' as shown in code segment 1 above.
The question explicitly states: "Can a View be created using a CTE as part of its defining SELECT statement.?" The emphasized part makes it very clear that the question was not about creating a view using only a CTE as its definining SELECT statement.
April 21, 2010 at 2:52 am
[font="Verdana"]
Hugo Kornelis (4/21/2010)
Good, clean question, Ron. Nice work!abrar.ahmad-1058946 (4/21/2010)
I conceived your quest statement as 'The Single Defining Select Statement of View Definition' as shown in code segment 1 above.The question explicitly states: "Can a View be created using a CTE as part of its defining SELECT statement.?" The emphasized part makes it very clear that the question was not about creating a view using only a CTE as its definining SELECT statement.
following seems cool to me
Do body of a view can contain CTE/s?
Do we can use CTEs within a view defintion?
But i appreciate your "emphasized part" understanding, and would try to correct myself in future.
Thanks buddy.
[/font]
April 21, 2010 at 6:18 am
Good question today. No scope for criticism 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 21, 2010 at 6:49 am
Many times the questions are ambiguous or semantically misleading. This was not one of those times. Also, I learned something new. Thanks for the good question.
April 21, 2010 at 7:16 am
Good question. I agree, no scope for criticism. Also, I have made such a view, so I know it can be done. 🙂
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 21, 2010 at 7:21 am
Nice QOD! Does anyone have a link to a view that uses a CTE in its definition? I had never even considered doing this and would like to see an example if anyone has a fairly simple one.
Thanks,
Kenny
April 21, 2010 at 7:37 am
Kenneth Wymore (4/21/2010)
Does anyone have a link to a view that uses a CTE in its definition?
Go to the page referenced in the explanation of the question; scroll all the way down to the community content; check out the second example.
April 21, 2010 at 7:42 am
Good QOD:-)
TNX
Michael
April 21, 2010 at 7:42 am
Oops, I glossed over that before. Thanks Hugo!
April 21, 2010 at 8:56 am
Good straight forward question! Thanks!
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 21, 2010 at 9:03 am
Hugo Kornelis (4/21/2010)
Kenneth Wymore (4/21/2010)
Does anyone have a link to a view that uses a CTE in its definition?Go to the page referenced in the explanation of the question; scroll all the way down to the community content; check out the second example.
I usually just look at help from Management Studio. I didn't realize that the website had community content. Thanks for pointing that out.
April 21, 2010 at 9:42 am
Good question! Thank you Ron.
About 2 years ago I read the BOL reference article on common table expressions and since that time I clearly remember that it stated in the remarks section that "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon". Since that time I got into habit of always starting the cte expression with a semicolon, i.e.
;with records (some_columns) as
(
select ...
)
Because of that reference I answered today's question incorrectly figuring that surely the statement like this:
create view view_name as
;with records (some_columns) as
(
select ...
) -- etc
will never fly due to that semicolon. This taught me a lesson to read the BOL remarks more carefully. The one about semicolon talks about the batch but if the view definition has only one statement in it then no semicolon is required before the cte.
Oleg
April 21, 2010 at 9:59 am
Thanks Ron. This was a good question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply