WITH (Subquery_Factoring_Clause) 9i

The WITH clause lets you use the results of a complex query more than once. Using the WITH clause, Oracle retrieves the results of a query and stores them in the user's temporary tablespace. It does not support recursive use of the WITH clause.

With DeptTempTbl As (Select DeptNo,
                            Count(*) As DeptRecCnt
                       From Emp
                   Group By Deptno)
/*-------------------------------------------------------------*/
/* Use the results from the above query in the following query */
/* and subquery                                                */
/*-------------------------------------------------------------*/
Select DeptNo,
       DeptRecCnt
  From DeptTempTbl
 Where DeptRecCnt >= (Select Avg(DeptRecCnt)
                        From DeptTempTbl);

    DEPTNO DEPTRECCNT
---------- ----------
        30          6
        20          5