CTE - Can I Use More Than One Temporary Result Set?

Question

I would like to use CTE to create a temporary result set. I then want to create another temporary result set that uses the first result set as input. Is this possible? The code below attempts to do this but I run into the error : "Invalid column name 'ptask_id'" Thanks in advance.

;WITH act_and_proc
 AS (SELECT *
       FROM afm_roleprocs
      WHERE role_name = 'GDI SYSTEM ADMIN'),

ptask_list AS
(SELECT ptask_id,
   activity_id,
   process_id,
   task_file
 FROM afm_ptasks pt
 WHERE     process_id IN (SELECT process_id
                        FROM act_and_proc ap
                       WHERE     pt.process_id = ap.process_id
                             AND pt.activity_id = ap.activity_id
                             AND pt.task_type = 'WEB URL'
                             AND pt.security_group != 'SYS-HIDDEN'
                             AND pt.is_hotlist = 0)
   AND process_id IN (SELECT process_id
                        FROM afm_processes
                       WHERE process_id = pt.process_id AND is_active = 1)
)

select * from ptask_list

The error has nothing to do with the CTE - it means that table `afm_ptasks` doesn't have a column named `ptask_id`. In any case, CTEs don't create temporary results sets. They are simply a syntactic element of the entire query. The query analyzer will process the entire query and decide how to execute it — Panagiotis Kanavos, Jul 11 '14 at 12:25
possible duplicate of [Keeping it simple and how to do multiple CTE in a query](http://stackoverflow.com/questions/2140750/keeping-it-simple-and-how-to-do-multiple-cte-in-a-query) — usr, Jul 11 '14 at 12:29
Sure you can use multiple ctes. Just separate them with a comma. — Sean Lange, Jul 11 '14 at 13:51

score 1 · Accepted Answer · answered Jul 11 '14 at 13:53

1

Sure. The syntax is like below.

with MyCte as
(
    Select SomeColumns from SomeTable
)
, MyNextCte as
(
    Select OneColumn from MyCte
)

select * from MyNextCte

answered Jul 11 '14 at 13:53

Sean Lange

33,028
3
25
40

CTE - Can I Use More Than One Temporary Result Set?

1 Answers1