Recursive Subquery Factoring - With Clause

Oracle Database 11g Release 2 introduces "Recursive Subquery Factoring", the successor to Connect By hierarchical querying.

Here are some examples of using recursive subquery factoring to understand its working easily:

Example 1: Print 1 to 10

with t(a) as
(
    select 1 as a from dual
    union all
    select a+1 from t where a< 10
)
select a from t

Example 2: Fibonacci Series

with mytab(a,b,c,lvl) as
(
    select 0 a, 1 b, 1 c, 1 lvl from dual
    union all
    select b,c,b+c, lvl+1  from mytab where lvl < 10
)
select lvl,a,b,c fib from mytab

More Examples
- Recursive Subquery Factoring Examples: Factorial and Power
- Recursive Subquery Factoring Examples: Employee Manager Hierarchy

Related Links
- SQL Interview Question Answers
- Oracle SQL Puzzles