BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog gives training on writing CTEs in SQL, or Common Table Expressions to give them their full title. The blog also includes a section on creating recursive CTEs.
- CTEs in SQL - Common Table Expressions
- Common Problems with CTEs (this blog)
- Recursive CTEs
You can find many more articles like this on our SQL tutorial page. To get a rounded view of how to program in SQL, have a look at our introductory and advanced SQL training.
Posted by Andy Brown on 20 October 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Common Problems with CTEs
Time and again when using Common Table Expressions I make the same 3 mistakes, so I thought I'd list them here so others might share the pain (and avoid making the same mistakes!).
Problem 1 - End Preceding Line with a Semi-Colon
In SQL, you can choose to end every command with a semi-colon (;), but it's usually optional. However, for a CTE it's essential:
-- the semi-colon is essential!
USE MOVIES;
-- get a CTE containing those films
-- which last longer than 3 hours
WITH LongFilms AS (
SELECT
FilmName,
FilmReleaseDate
FROM
tblFilm
WHERE
FilmRunTimeMinutes > 180
)
-- using this CTE, sort the rows
SELECT
FilmName,
CONVERT(char(10),FilmReleaseDate,103) AS 'Released'
FROM
LongFilms
ORDER BY
FilmReleaseDate
Without the semi-colon above, you'd get the following error:
The start of the error message, which is self-explanatory
Problem 2 - Missing Brackets
The brackets aren't optional!
-- get a CTE containing those films
-- which last longer than 3 hours
WITH LongFilms AS
SELECT
FilmName,
The query above is doomed to fail, since you should enclose the SELECT statement in parentheses.
Problem 3 - CTEs are like Mayflies
CTEs don't live long - in fact, just for a single command. This means that as soon as you've created a CTE you must use it. The following query would fail:
-- get a CTE containing those films
-- which last longer than 3 hours
WITH LongFilms AS (
SELECT
FilmName,
FilmReleaseDate
FROM
tblFilm
WHERE
FilmRunTimeMinutes > 180
)
-- do something else
PRINT 'This ruins everything!'
-- using this CTE, sort the rows (but it's too late now ...)
SELECT
FilmName,
CONVERT(char(10),FilmReleaseDate,103) AS 'Released'
FROM
LongFilms
ORDER BY
FilmReleaseDate
The moral? Don't be tempted to include any other statements after creating a CTE.
Note that there's a bit more to CTEs than is shown in this blog; you can see extra CTE tips and topics at this recent blog.
- CTEs in SQL - Common Table Expressions
- Common Problems with CTEs (this blog)
- Recursive CTEs