April 8, 2015 at 10:53 pm
Comments posted to this topic are about the item The CTE Problem
April 8, 2015 at 10:54 pm
Easy Question..!!
But, When you provide Alias Name, it should be "YearMonth" because CTE has Where Clause for YearMonth Column.
April 8, 2015 at 10:57 pm
Yeah, pretty easy on this one. Two hints is more than enough.
April 8, 2015 at 11:27 pm
I kept looking for a catch but it turned out this was just an easy question. 🙂 Thanks Steve!
April 9, 2015 at 12:52 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 9, 2015 at 1:19 am
At the time of posting, 69% have got this question right, but only 68% chose the second option...
April 9, 2015 at 1:52 am
Toreador (4/9/2015)
At the time of posting, 69% have got this question right, but only 68% chose the second option...
Probably rounding errors.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 9, 2015 at 2:08 am
This was removed by the editor as SPAM
April 9, 2015 at 2:22 am
I got it right so I'm not complaining, but technically the alias can be defined by providing a column list as per answer 4, specifically:
WITH MonthlyProductSales (YearMonth, ProductID, SumLineTotal) AS
So other than the difference between "should" and "needs" wording both 2 and 4 are correct answers.
April 9, 2015 at 3:50 am
What Matthew said.
I chose the answer that is marked as correct because that is the only one that really points out what is wrong - a column list by itself is not mandatory, it is just one of two possible ways to fulfill the requirement of naming each column. But it is important to note that this alias can be provided in two ways, either as an inline "AS alias" in the SELECT clause, or in a column list after WITH MonthlySales as indicated by option 4.
April 9, 2015 at 5:19 am
Hugo Kornelis (4/9/2015)
What Matthew said.I chose the answer that is marked as correct because that is the only one that really points out what is wrong - a column list by itself is not mandatory, it is just one of two possible ways to fulfill the requirement of naming each column. But it is important to note that this alias can be provided in two ways, either as an inline "AS alias" in the SELECT clause, or in a column list after WITH MonthlySales as indicated by option 4.
I 'm afraid I disagree, and so do both SQL Server itself and its documentation.
The cte-query-definition part of the clause (ie the bit in brackets after AS) has to meet all the requirements of a view definition, and views with unnamed columns are not permitted.
So option 4 is NOT a correct answer.
The requirements for the cte-query-definition to meet all the requirements of a view definition has been there since day 1 of CTEs in Sql Server.
Tom
April 9, 2015 at 5:44 am
I beg to differ - practical experience says that the column list is a valid place to alias CTE columns, no matter what the documentation may say.
I don't have the original tables for the query available, but I think you will agree when you look at it that the following CTE which returns 100 rows only names the column "N" in the Column List - and that the function row_number () over () has no alias against it. It executes without error on SQL Server 2008.
with
NUMTAB (N) as (
select row_number() over(order by (select null))
from
(values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (i),
(values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (i)
)
select N from NUMTAB;
April 9, 2015 at 6:16 am
TomThomson (4/9/2015)
Hugo Kornelis (4/9/2015)
What Matthew said.I chose the answer that is marked as correct because that is the only one that really points out what is wrong - a column list by itself is not mandatory, it is just one of two possible ways to fulfill the requirement of naming each column. But it is important to note that this alias can be provided in two ways, either as an inline "AS alias" in the SELECT clause, or in a column list after WITH MonthlySales as indicated by option 4.
I 'm afraid I disagree, and so do both SQL Server itself and its documentation.
The cte-query-definition part of the clause (ie the bit in brackets after AS) has to meet all the requirements of a view definition, and views with unnamed columns are not permitted.
So option 4 is NOT a correct answer.
The requirements for the cte-query-definition to meet all the requirements of a view definition has been there since day 1 of CTEs in Sql Server.
You are correct, and you are not.
Yes, the requirements for a CTE in this regard are the same as for a view.
No, that does not make option 4 incorrect. In a view, just as in a CTE, columns may be named either with an AS clause in the SELECT list, or in a column list in the defintion.
CREATE VIEW dbo.TestIt (Col1, Col2)
AS
SELECT 1, 2;
EDIT: To add this: As far as documentation goes, you are incorrect here as well. See https://msdn.microsoft.com/en-us/library/ms175972.aspx: "The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition". There is similar wording on the page on CREATE VIEW.
April 9, 2015 at 6:18 am
Nice question. And yes, the correct option is correct.
April 9, 2015 at 6:23 am
The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition. As they were not, I see both 2 and 4 as being correct. Supplying either the alias or column names corrects the error.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply