Sql – Standard alternative to CONNECT BY

ansi-sqloraclesql

I'm trying to convert some Oracle SQL queries to work with (in theory) any SQL database. Some of the queries are hierarchical in nature and are written using CONNECT BY.

Is there a standard SQL alternative to Oracle's START WITH…CONNECT BY syntax? Or is there some recommended process I should follow to convert the hierarchical queries?

Best Answer

In Oracle 11gR2 they support recursion in Common Table Expressions (what most Oracle people know as sub-querying factoring, i.e. the WITH clause). As this is the ANSI way of doing things it ought to be more portable.

Tom Kyte writes about Recursive Subquery Factoring in the November 09 edition of Oracle magazine.