This page has 0 threads | Add post

Common Problems with CTEs

Written by Andy Brown

In this tutorial

Problem 1 - End Preceding Line with a Semi-Colon
Problem 2 - Missing Brackets
Problem 3 - CTEs are like Mayflies

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.

Previous
tutorial

Subqueries

Previous
part

CTEs in SQL - Common Table Expressions

Top of page

Next
part

Combining more than one CTE in the same query

Next
tutorial

Programming in SQL

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

CTEs and derived tables

Common Problems with CTEs

Problem 1 - End Preceding Line with a Semi-Colon

Problem 2 - Missing Brackets

Problem 3 - CTEs are like Mayflies

Head office

London

Manchester