On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, May 26, 2008

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

No comments:

Post a Comment