April 9, 2015 at 6:35 am
Thanks for the question. Couldn't find where YearMonth was defined so that had to be it.
April 9, 2015 at 6:37 am
Hugo Kornelis (4/9/2015)
...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.
Thanks, I didn't realize the columns could be specified up front.
April 9, 2015 at 6:57 am
jpatenaude (4/9/2015)
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.
+1
April 9, 2015 at 7:20 am
Ugh, had a hard time choosing between the two correct answers.
I went with the column name list, since the documentation seemed to favor that syntax. 🙁
April 9, 2015 at 8:02 am
I chose option 2 because not having an alias on that column is bad to me, however I agree that option 4 also "fixes" it, just not in an agreeable way.
Now, what I really wanted to pick was the option that is not there...
o The predicate is not SARGable due to the poor construction of the query, move the WHERE clause into the CTE and change it to a range selection on the date column. Then remove the CTE and the outer SELECT and stop trying to obfuscate your code.
:w00t:
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 9, 2015 at 10:16 am
matthew.flower (4/9/2015)
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.
+1
(I learned using CTEs by understanding the MSDN examples, and each one has the column list mentioned, so never did or tried or wrote any CTEs without column list. Good to know either way of aliasing works)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
April 9, 2015 at 10:18 am
Hugo Kornelis (4/9/2015)
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.
Interesting. You definitely have a case where not naming all the columns works provided the name list is provided. So clearly you are right.
But I've had cases where it didn't. Or at least I think I have - I shall have to try to construct one again.
I wonder if the cases I remember were ones where two columns had the same name in the rowset generated by the select - I've certainly had cases where having the namelist didn't work but adding an alias did, and I thought they were cases where that column had been (carelessly) left nameless. And I interpreted the sentence you quote as just saying that if your column names were not unique you had to fix that (either by aliasing to make the unique or) by having the column name list (which has to provide unique names) which is clearly wrong with an example of missing column name working.
Tom
April 9, 2015 at 10:24 am
I picked #4, but I can see the case for #2 being the more correct answer. At least I came close.
April 9, 2015 at 3:19 pm
TomThomson (4/9/2015)
I've certainly had cases where having the namelist didn't work but adding an alias did
I hope you can reconstruct those, since they would be very interesting to look at. My understanding is that this should not be possible.
And I interpreted the sentence you quote as just saying that if your column names were not unique you had to fix that (either by aliasing to make the unique or) by having the column name list (which has to provide unique names) which is clearly wrong with an example of missing column name working.
I see how easy it is to misinterpret that sentence. It has two key phrases: "distinct" and "are supplied". But it is very easy to focus too much on the first and therefor overlook the latter.
April 9, 2015 at 3:27 pm
Adding the proper column names in parentheses after the CTE name, would equally correct the CTE, so I think both of those answers are technically correct...
;WITH MonthlyProductSales(YearMonth,ProductID,SumLineTotal) AS
(
SELECT CONVERT(CHAR(7),ModifiedDate,120)
, ProductID
, SUM(LineTotal) AS SumLineTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120)
)
-- SELECT statement referencing CTE
SELECT * FROM MonthlyProductSales
WHERE YearMonth > '2008-06';
April 12, 2015 at 4:22 pm
Hugo Kornelis (4/9/2015)
TomThomson (4/9/2015)
I've certainly had cases where having the namelist didn't work but adding an alias didI hope you can reconstruct those, since they would be very interesting to look at. My understanding is that this should not be possible.
My best guess at the moment is that my memory is playing me false - I can't reconstruct it at all, despite trying hard.
Tom
April 12, 2015 at 10:14 pm
Guys
The below query works. I think per CTE, before "AS" we need to add the column names which were missing. I chose Option #4. Not sure, why Option#4, is not a right choice.
WITH MonthlyProductSales (Date, ProductID,SumLineTotal)
AS
(
SELECT CONVERT(CHAR(7),ModifiedDate,120) AS "Date"
, ProductID
, SUM(LineTotal) AS SumLineTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120)
)
-- SELECT statement referencing CTE
SELECT * FROM MonthlyProductSales
Thanks.
April 17, 2015 at 8:12 am
"There should be a column list after the "WITH MonthlyProductSales" "
is also correct. If one adds a column list then there is absolutely no need to name the fields of the select clause that follows in the SELECT pertained in the CTE statement.
Nothing that has not been done before.
June 3, 2015 at 6:09 am
Nice one
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply