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.