WITH Clause Query Fails With ORA-32031 (Doc ID 852140.1)

Starting with Oracle versions 9i and onward, the "WITH" clause was introduced to allow Subquery Factoring.

The WITH clause can help improve performance and also readability by enabling a "SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times.

With early versions like 11.1.0.x,  a limitation was present in that the subquery does not support recursion e.g. referencing the subquery alias from inside the subquery.

With 11.2 and onward, recursive subquery factoring is supported.

Reference:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2077142

The technique seen below is used within other vendor databases such as Microsoft SQL Server and it is known as CTE -Common Table Expression.

The following are results when connected to an 11.1 database.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.