Creating and Joining CTEs in SQL

  • Eric

    SSC Enthusiast

    Points: 177

    I am new fairly new to SQL coding.  I am trying to write and join two CTEs to return one data set so that I can create a master CTE that will return the data desired by the end user.  I am running in circles chasing the error messages. Code Sample

  • Lynn Pettis

    SSC Guru

    Points: 442144

    Please post the code, not a picture of your code.

    Also, trying to read your picture of your code I see part of your problem.  CTEs do not START with a semicolon.

     

    • This reply was modified 7 months ago by  Lynn Pettis.
  • John Mitchell-245523

    SSC Guru

    Points: 148356

    You need a parenthesis to close your first CTE definition, and you need to separate it from the second one with a comma, not a semi-colon.

    John

  • Thom A

    SSC Guru

    Points: 98404

    This is one reason why I dislike people being taught that a CTE "starts" with a semi-colon (;), because it doesn't. ; is a statement terminator, not a statement "beginningator" or a "CTE/MERGE starter". It goes at the end of your statements and that's where it should stay.

    When you get the error "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." it's not telling you to "start" your CTE expression with a semi colon, it's telling you you forgot to terminate the statement prior to it. The statement prior to the CTE (and all of them really) should have a ; at the end, and the error is telling you to do that.

    To declare multiple Common Table Expressions in a single statement you need to comma delimit them. I can't use your code (it's an image), so here's an example instead:

    DECLARE @ID int; --semi colon goes here
    SET @ID = 1;--semi colon goes here

    --Next line (start of the CTE) does not start with a ;
    WITH Users AS(
    SELECT *
    FROM (VALUES(1,'Steve'),
    (2,'Thom'),
    (3,'Eric'),
    (4,'Gail'))V(UserID,Username)), --comma to mean another CTe is coming
    Posts AS(
    SELECT *
    FROM (VALUES(1,'This is a post',1),
    (2,'Here''s the solution you need!',3),
    (3,'How are you today?',1))V(PostID, Content,UserID))
    SELECT *
    FROM Users U
    JOIN Posts P ON U.UserID = P.UserID
    WHERE U.UserID = @ID; --Semi colon goes here

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Eric

    SSC Enthusiast

    Points: 177

    USE DBSource

    GO

    WITH Cte1 AS

    (SELECT T.Term

    ,T.TermEnd

    ,ROW_NUMBER() OVER(ORDER BY T.TermSort) AS RowNum

    FROM Term

    WHERE RIGHT(T.Term,2) IN ('SP','FA') AND T.TermReportingYear >= 2010

    )

    ,Cte2 AS

    (SELECT ID

    ,StuLevelStartTerm

    FROM dbo.StudentAcadlevel

    WHERE ID = '1221417'

    )

    SELECT *

    FROM Cte1,offsetrow

    JOIN Cte2,offsetrow

    ON TC.Term = SC.StuLevelStartTerm

    WHERE Cte1.RowNum > offset.RowNum AND Cte2.RowNum > offset.RowNum

    ;

    And This is the error I get now.  Thank you for your time.

    Msg 102, Level 15, State 1, Line 23

    Incorrect syntax near ','.

  • Thom A

    SSC Guru

    Points: 98404

    Binned this answer

    • This reply was modified 7 months ago by  Thom A. Reason: Yeah, more errors than I first though
    • This reply was modified 7 months ago by  Thom A. Reason: Binned

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Lynn Pettis

    SSC Guru

    Points: 442144

    Take a really close look at your JOIN clause in the final select.  I am not sure what you are trying to do as you have commas after the names of the cte's and the same "alias" for both but then have different table aliases in the ON clause of the join.

     

  • Lynn Pettis

    SSC Guru

    Points: 442144

    Thom A wrote:

    This is one reason why I dislike people being taught that a CTE "starts" with a semi-colon (;), because it doesn't. ; is a statement terminator, not a statement "beginningator" or a "CTE/MERGE starter". It goes at the end of your statements and that's where it should stay. When you get the error "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." it's not telling you to "start" your CTE expression with a semi colon, it's telling you you forgot to terminate the statement prior to it. The statement prior to the CTE (and all of them really) should have a ; at the end, and the error is telling you to do that. To declare multiple Common Table Expressions in a single statement you need to comma delimit them. I can't use your code (it's an image), so here's an example instead:

    DECLARE @ID int; --semi colon goes here
    SET @ID = 1;--semi colon goes here

    --Next line (start of the CTE) does not start with a ;
    WITH Users AS(
    SELECT *
    FROM (VALUES(1,'Steve'),
    (2,'Thom'),
    (3,'Eric'),
    (4,'Gail'))V(UserID,Username)), --comma to mean another CTe is coming
    Posts AS(
    SELECT *
    FROM (VALUES(1,'This is a post',1),
    (2,'Here''s the solution you need!',3),
    (3,'How are you today?',1))V(PostID, Content,UserID))
    SELECT *
    FROM Users U
    JOIN Posts P ON U.UserID = P.UserID
    WHERE U.UserID = @ID; --Semi colon goes here

    The problem is that people are taught to do this as a crutch to insure that the previous statement is terminated by a semicolon instead of being taught to properly terminate their SQL statements.  Yes, they were optional but it is getting to the point that they are actually becoming mandatory.  Just makes sense to use them properly as terminators instead of beginninators.

  • Eric

    SSC Enthusiast

    Points: 177

    Ok I have changed the code.  I added a things in the original code just because I was desperate. I tried changing the code closer to the original way that I wrote it.

    USE Warehouse

    GO

    WITH Cte1 AS

    (SELECT T.Term

    ,T.TermEnd

    ,ROW_NUMBER() OVER(ORDER BY T.TermSort) AS RowNum

    FROM Term

    WHERE RIGHT(T.Term,2) IN ('SP','FA') AND T.TermReportingYear >= 2010

    )

    ,Cte2 AS

    (SELECT ID

    ,StuLevelStartTerm

    FROM dbo.StudentAcadlevel

    WHERE ID = '1221417'

    )

    SELECT *

    FROM Cte1

    JOIN Cte2

    ON Cte1.Term = Cte2.StuLevelStartTerm

     

    Error: Msg 4104, Level 16, State 1, Line 10

    The multi-part identifier "T.Term" could not be bound.

    Msg 4104, Level 16, State 1, Line 10

    The multi-part identifier "T.Term" could not be bound.

    Msg 4104, Level 16, State 1, Line 10

    The multi-part identifier "T.TermReportingYear" could not be bound.

    Msg 4104, Level 16, State 1, Line 5

    The multi-part identifier "T.Term" could not be bound.

    Msg 4104, Level 16, State 1, Line 6

    The multi-part identifier "T.TermEnd" could not be bound.

    Msg 4104, Level 16, State 1, Line 7

    The multi-part identifier "T.TermSort" could not be bound.

  • Thom A

    SSC Guru

    Points: 98404

    There a few problems here, and each time I look at it I find another.

    Firstly you are using the ‘’ characters, where you should be using ' for literal strings. SELECT ‘1221417’; will cause an error, it should be SELECT '1221417';.

    Next, your FROM in your outer select has Cte1,offsetrow and Cte2,offsetrow. This doesn't make sense for 2 reasons. Firstly commas in the FROM means that you are using comma delimiting objects, like you would with the old ANSI89 JOIN syntax (like SELECT * FROM Table1, Table2 WHERE Table1.pID = Table2.fID). I doubt that's what you want and mean a ., but that still doesn't make sense; the FROM doesn't references the column of a table it just references the object. In your ON you then reference the object TC and SC, which don't exist in your FROM. Considering that the objects you reference are Termand StudentAcadlevel in the CTEs CTE1 and CTE2 respectively I suspect you want:

    FROM Cte1 TC
    JOIN Cte2 SC ON TC.Term = SC.StuLevelStartTerm

    Finally you have Cte1.RowNum > offset.RowNum AND Cte2.RowNum > offset.RowNum. I've no idea here I'm afraid. OFFSET hasn't appeared in your query till now, so i don't know what the goal is here.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • ZZartin

    SSC-Dedicated

    Points: 30376

    Run just the query from the first CTE 🙂

  • Thom A

    SSC Guru

    Points: 98404

    Eric wrote:

    Ok I have changed the code.  I added a things in the original code just because I was desperate. I tried changing the code closer to the original way that I wrote it.

    Please use the "insert/edit code sample" button 🙂 It makes it far more readable 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Lynn Pettis

    SSC Guru

    Points: 442144

    Give the following a try:

    WITH [Cte1] AS (
    SELECT
    [T].[Term]
    ,[T].[TermEnd]
    ,[RowNum] = ROW_NUMBER() OVER(ORDER BY [T].[TermSort])
    FROM
    [Term] AS [T]
    WHERE
    RIGHT([T].[Term],2) IN ('SP','FA')
    AND [T].[TermReportingYear] >= 2010
    )
    ,[Cte2] AS (
    SELECT
    [sst].[ID]
    , [sst].[StuLevelStartTerm]
    FROM
    [dbo].[StudentAcadlevel] AS [sst]
    WHERE
    [sst].[ID] = '1221417'
    )
    SELECT
    *
    FROM
    [Cte1]
    INNER JOIN [Cte2]
    ON [Cte1].[Term] = [Cte2].[StuLevelStartTerm];
  • Thom A

    SSC Guru

    Points: 98404

    Lynn Pettis wrote:

    The problem is that people are taught to do this as a crutch to insure that the previous statement is terminated by a semicolon instead of being taught to properly terminate their SQL statements.  Yes, they were optional but it is getting to the point that they are actually becoming mandatory.  Just makes sense to use them properly as terminators instead of beginninators.

    Honestly, in my view, people taught to put a semicolon at the start of a CTE is just utter laziness on behalf of the teacher; as they see no good reason to teach people properly. And, in the end, it ends up teaching people wrong (as this user has proved with their syntax ;WITH CTE AS(...), ;CTE2 AS...).

    Unfortunately these people teaching such standards are a real problem, and are probably the same people who aren't teaching students how to parametrise a query properly in their application. Hence why you still see code like sqlQuery = "SELECT * FROM MyTable WHERE CustomerName = '" & Name.text &"'"; on an almost daily occurance on websites like SSC and Stack Overflow...

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Eric

    SSC Enthusiast

    Points: 177

    OK Thank you everyone!  The code is now working properly!

    USE DBSource
    GO

    WITH Cte1 AS
    (SELECT T.Term
    ,T.TermEnd
    ,ROW_NUMBER() OVER(ORDER BY T.TermSort) AS RowNum

    FROM Term AS T
    WHERE RIGHT(T.Term,2) IN ('SP','FA') AND T.TermReportingYear >= 2010

    )
    ,Cte2 AS
    (SELECT ID
    ,StuLevelStartTerm

    FROM dbo.StudentAcadlevel AS SAL
    --WHERE ID = '1221417'

    )
    SELECT *
    FROM dbo.Term AS T
    JOIN dbo.StudentAcadlevel AS SAL
    ON T.Term = SAL.StuLevelStartTerm

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply