The CTE Problem

  • Comments posted to this topic are about the item The CTE Problem

  • Easy Question..!!

    But, When you provide Alias Name, it should be "YearMonth" because CTE has Where Clause for YearMonth Column.

  • Yeah, pretty easy on this one. Two hints is more than enough.

  • I kept looking for a catch but it turned out this was just an easy question. 🙂 Thanks Steve!

  • Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • At the time of posting, 69% have got this question right, but only 68% chose the second option...

  • 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

  • This was removed by the editor as SPAM

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

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

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

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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