This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Common Table Expressions (CTEs) - SQL Training Part two of a three-part series of blogs
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.

Common Table Expressions (CTEs) - SQL Training

Part two of a three-part series of blogs

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

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.

Parts of this blog
CTEs in SQL - Common Table Expressions Common Problems with CTEs (this blog) Recursive CTEs

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

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