The WITH query_name clause lets you assign a name to a subquery
block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle
optimizes the query by treating the query name as either an inline view or as a temporary table.
You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is
visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.
A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the
query such as where one averaged value needs to be compared against two or three times.
Restrictions on Subquery Factoring:
You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause
within the subquery of another subquery_factoring_clause. However, a query_name defined in one subquery_factoring_clause
can be used in the subquery of any subsequent subquery_factoring_clause.
In a query with set operators, the set operator subquery cannot contain the
subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clause.
WITH <alias_name> AS
(subquery_sql_statement) SELECT <column_name_list> FROM <alias>;
WITH col_generator AS ( SELECT t1.batch_id, DECODE(t1.action, 'SENT', t1.actdate) sent,
DECODE(t2.action,'RECV', t2.actdate) received FROM test t1, test t2 WHERE t2.batch_id(+) = t1.batch_id) SELECT batch_id, max(sent) sent, max(received) received FROM col_generator GROUP BY batch_id ORDER BY 1;
Another WITH Clause demo posted by Maxim Demenko to comp.databases.
This demo takes an input of numbers, in seconds and returns