René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

with [Oracle SQL]

create table w (a number, b varchar2(10));

insert into w values (4, 'four');
insert into w values (8,'eight');
insert into w values (9, 'nine');
insert into w values (1,  'one');

with v as (select b from w where a > 5)
select * from v;
Such a query is not too useful as it can also be written with a simple select * from w.
It is more useful however, if the query that is named with the with statment is used more than once as a subquery.

Example: all permutations of 1..4

select m1, m2, m3, m4
  from (
    with one_to_four as (
      select 1 n from dual union
      select 2 n from dual union
      select 3 n from dual union
      select 4 n from dual
    )
    select n1.n m1, n2.n m2, n3.n m3, n4.n m4
    from
      one_to_four n1 cross join
      one_to_four n2 cross join 
      one_to_four n3 cross join 
      one_to_four n4
    where
      n1.n not in (n2.n, n3.n, n4.n) and
      n2.n not in (      n3.n, n4.n) and
      n3.n not in (            n4.n)
  );
The result: 
        M1         M2         M3         M4
---------- ---------- ---------- ----------
         1          2          3          4
         1          2          4          3
         1          3          2          4
         1          3          4          2
         1          4          2          3
         1          4          3          2
         2          1          3          4
         2          1          4          3
         2          3          1          4
         2          3          4          1
         2          4          1          3
         2          4          3          1
         3          1          2          4
         3          1          4          2
         3          2          1          4
         3          2          4          1
         3          4          1          2
         3          4          2          1
         4          1          2          3
         4          1          3          2
         4          2          1          3
         4          2          3          1
         4          3          1          2
         4          3          2          1