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?
WITH HI_table1 AS
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 ','.
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
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.
Well, glad there was some wider learning as part of this.
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.