ASA SQL User's Guide
  Common Table Expressions
    Recursive common table expressions

Selecting hierarchical data

The following query demonstrates how to list the employees by management level. Level 0 represents employees with no managers. Level 1 represents employees who report directly to one of the level 0 managers, level 2 represents employees who report directly to a level 1 manager, and so on.

WITH RECURSIVE
  manager ( emp_id, manager_id,
            emp_fname, emp_lname, mgmt_level ) AS
( ( SELECT emp_id, manager_id,       -- initial subquery
           emp_fname, emp_lname, 0
    FROM employee AS e
    WHERE manager_id = emp_id )
  UNION ALL
  ( SELECT e.emp_id, e.manager_id,   -- recursive subquery
           e.emp_fname, e.emp_lname, m.mgmt_level + 1
    FROM employee AS e JOIN manager AS m
     ON   e.manager_id =  m.emp_id
      AND e.manager_id <> e.emp_id
      AND m.mgmt_level < 20 ) )
SELECT * FROM manager
ORDER BY mgmt_level, emp_lname, emp_fname

The condition within the recursive query that restricts the management level to less than 20 is an important precaution. It prevents infinite recursion in the event that the table data contains a cycle.

The MAX_RECURSIVE_ITERATIONS option 

The option MAX_RECURSIVE_ITERATIONS is designed to catch runaway recursive queries. The default value of this option is 100. Recursive queries that exceed this number of levels of recursion terminate, but cause an error.

Although this option may seem to diminish the importance of a stop condition, this is not usually the case. The number of rows selected during each iteration may grow exponentially, seriously impacting database performance before the maximum is reached. Stop conditions within recursive queries provide a means of setting appropriate limits in each situation.