Creating and Joining CTEs in SQL

Question

Post reply

Creating and Joining CTEs in SQL

Eric

SSC Enthusiast

Points: 177
More actions
April 12, 2019 at 2:28 pm

#3157935

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.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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 5 years ago by Lynn Pettis.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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: 99126 More actions · Answer 3

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.
Larnu.uk

Eric SSC Enthusiast Points: 177 More actions · Answer 4

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: 99126 More actions · Answer 5

Binned this answer

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

Thom~

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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: 442467 More actions · Answer 7

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 More actions · Answer 8

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: 99126 More actions · Answer 9

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.
Larnu.uk

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 10

ZZartin

SSC-Dedicated

Points: 30894

April 12, 2019 at 3:12 pm

#3160160

Run just the query from the first CTE 🙂

Thom A SSC Guru Points: 99126 More actions · Answer 11

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.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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: 99126 More actions · Answer 13

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.
Larnu.uk

Eric SSC Enthusiast Points: 177 More actions · Answer 14

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