One of the new features of Oracle 11G is Recursive Subquery Factoring (With Clause).
It is of the below format:
with query_name(col_alias1,col_alias2)
as
(
/*Below subquery_factoring_clause, Must contain 2 Query blocks : Anchor Member and Recursive Member */
select col_alias1 , col_alias2 from dual ===> Anchor Member
==> You must combine the recursive member with the anchor member using the UNION ALL set operator
UNION ALL
select col_alias1 , col_alias2 from query_name ===> Recursive Member
) select * from query_name
Few Rules:
1. You must combine the recursive member with the anchor member using the UNION ALL set operator
2. The anchor member must appear before the recursive member, and it cannot reference query_name.
3. The number of column aliases following WITH query_name and the number of columns in the SELECT lists of the anchor and recursive query blocks must be the same.
Example- Fibonacci Series:
with tdata(idx,fb,prev)
as
(select 1 idx, 0 fb ,cast(null as number) prev from dual
union all
select f1.idx+1 idx,(nvl(f1.prev,1) + f1.fb) fb,f1.fb prev from tdata f1
where f1.idx < 10
)
select fb from tdata order by fb;
References:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm
http://www.plsqlchallenge.com/pls/apex/f?p=10000:659:6978114797747::NO:659:P659_COMP_EVENT_ID,P659_QUIZ_ID,P659_QUESTION_ID:175266,,8032&cs=16C7A6293B4B41E088AF6F90A13F58D4B
