Creating CTE table in SQL not working

Q&A DQ Courses
#1

I have tried creating a CTE table through wrapping up a select statement with the appropriate CTE expression (i.e. using 'WITH HI_table1 AS + brackets) but this didn’t run and came with syntax error in SQL server. the code works with the select statement so I don’t see why it doesn’t work when I then turn it into a CTE, is there something wrong?
My Code: 

WITH HI_table1 AS
(

SELECT 
t1.x
, t1.y

FROM Table 1 AS t1 
LEFT JOIN Table 2 AS t2
ON t1.a= t2.a 
)

What actually happened:

Msg 103010, Level 16, State 1, Line 10
Parse error at line: 1, column: 1: Incorrect syntax near ','.
#2

I’m not good with SQL, but from looking at you code, there doesn’t seem to be anything wrong.

Are you writing the code within DQ’s platform or is it on your own computer?

And what happens if you add SELECT * FROM HI_table1 after the whole WITH block?

#3

this is run on my own computer in MS SQL server

thanks for your response :slight_smile:

if you run a SELECT * after WITH that wouldn’t follow the right syntax, which doesn’t have s further SELECT statement.
https://www.sqlshack.com/sql-server-common-table-expressions-cte/

2 Likes
#4

This should work.

Either I’m misunderstanding what you’re saying, or this is incorrect. Can you please try it and report back?

2 Likes
#5

Oh I see.

Yeah, I suggested that based on what I read here: WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Learn.

Though looking at your link, there seems to be a SELECT after the WITH.

;with ROWCTE(ROWNO) as  
   (  
     SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM sys.databases 
WHERE database_id <= 10
    )  
 
SELECT * FROM ROWCTE

It’s not directly after WITH, but after the whole code that is part of WITH.

Also, do you have two aliases for each table?

FROM Table 1 AS t1 
LEFT JOIN Table 2 AS t2
2 Likes
#6

OK, it’s working!

I was trying to test what the table looks like without selecting anything after, it seems when you start a statement with ‘With [table name] AS’ you need to select something alongside it otherwise nothing will be produced? I was under the impression that you could create a table ending in , for example the FROM, WHERE etc and it would compute. Instead, the CTE only seems to work when you subsequently ‘SELECT’ afterwards?

Thanks again and excuse the error in grasping this from the guidance

2 Likes
#7

Yeah, to be honest, I didn’t know that an error will be produced if we only have the CTEs with no accompanying SELECT at the end. So, in a way, I’m thankful that you brought the question up or otherwise I would be ignorant about that fact.

From reading this StackOverflow thread, it seems that if there’s only a single CTE, the CTE needs to be selected/joined with an outer/main query or else the engine will deem it an error. The engine doesn’t mind multiple unused CTEs as long as one of them is selected by an outer query. It’s probably for optimization rather than syntactical reasons, since it would be wasteful if the tables from the CTEs are created yet no outer query is using them.

Just my speculation, not exactly a fact.

2 Likes
#8

Well, glad there was some wider learning as part of this. :slight_smile:
I am trying to get to grips with SQL and it’s not easy troubleshooting on my own without someone to ask next to me, it’s good to have the forum to check in with.
StackOverflow is great, sometimes the answer isn’t quite fitting to the question but it’s a great resource.

2 Likes