none
CTE don't work including examples from MSDN, why?

    Question

  • This one for example:

    USE AdventureWorks2012;
    GO
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
    AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )

    or like this

    ;WITH qwe 
    AS
    (SELECT 1)
    Error:Incorrect syntax near ')'.

    I've found that I should put ";" before "With" clause, It doesn't help.

    What is going on?

    Saturday, April 20, 2013 2:06 PM

Answers

  • You are missing the outer query:

    USE AdventureWorks2012;
    GO
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
    AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
    SELECT * FROM Sales_CTE;

    CTE query (like subquery) does not work by itself. It requires an outer (main) query.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, April 20, 2013 2:08 PM
  • A common table expression is only a defintion like a view for one T-SQL statement. So, while you have defined it, you haven't used. Thus:

    USE AdventureWorks2012;
    GO
    
    WITH    Sales_CTE
              AS ( SELECT   SalesPersonID ,
                            SalesOrderID ,
                            YEAR(OrderDate) AS SalesYear
                   FROM     Sales.SalesOrderHeader
                   WHERE    SalesPersonID IS NOT NULL
                 )
        SELECT  *
        FROM    Sales_CTE;
    
    -- or
    WITH    qwe ( bla )
              AS ( SELECT   1
                 )
        SELECT  *
        FROM    qwe;

    btw, a CTE doesn't start with a semi-colon. This is only needed when you haven't correctly placed the semi-colon as T-SQL statement terminator at the end of the last statement directly before the CTE.
    • Marked as answer by Max Nevermind Saturday, April 20, 2013 2:23 PM
    Saturday, April 20, 2013 2:10 PM

All replies

  • You are missing the outer query:

    USE AdventureWorks2012;
    GO
    -- Define the CTE expression name and column list.
    WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
    AS
    -- Define the CTE query.
    (
        SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
        FROM Sales.SalesOrderHeader
        WHERE SalesPersonID IS NOT NULL
    )
    SELECT * FROM Sales_CTE;

    CTE query (like subquery) does not work by itself. It requires an outer (main) query.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, April 20, 2013 2:08 PM
  • A common table expression is only a defintion like a view for one T-SQL statement. So, while you have defined it, you haven't used. Thus:

    USE AdventureWorks2012;
    GO
    
    WITH    Sales_CTE
              AS ( SELECT   SalesPersonID ,
                            SalesOrderID ,
                            YEAR(OrderDate) AS SalesYear
                   FROM     Sales.SalesOrderHeader
                   WHERE    SalesPersonID IS NOT NULL
                 )
        SELECT  *
        FROM    Sales_CTE;
    
    -- or
    WITH    qwe ( bla )
              AS ( SELECT   1
                 )
        SELECT  *
        FROM    qwe;

    btw, a CTE doesn't start with a semi-colon. This is only needed when you haven't correctly placed the semi-colon as T-SQL statement terminator at the end of the last statement directly before the CTE.
    • Marked as answer by Max Nevermind Saturday, April 20, 2013 2:23 PM
    Saturday, April 20, 2013 2:10 PM