common table expression note…

Note: To anyone who is not an experienced sql programmer, this brief article is extremely technical and will probably be boring.  If you’re not a sql programmer you can probably skip this one.

The easiest answer to “what is a CTE” is …

a pre-populated temp table that is merely defined without using a temp table

It’s easy to do.  The syntax is something like this:
—————-
WITH
AS
(select *
from pr
where wbs1 = ‘myprojectnumber’)

select wbs1, wbs2, wbs3, longname, fee from where sublevel = ‘N’
————–

Basically by using the CTE I’m creating the temp table or table variable and populating it (and defining its columns) all in one step… just like a sub query… except that I can use it in several statements down the road all in the same batch.

Hint: if you happen to run sql profiler in while running Deltek Vision because you’re trying to figure out how something is done, and the statements you extract have temp tables in them, you can replace them quite easily with a CTE.

Took me about 5 minutes once I figured out what a CTE does and how it’s defined.

Comments or questions for Loren?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s