Standard alternative to CONNECT BY?

Question

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?

Answer 1

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.

Answer 2

If you need hierarchical queries against databases that don't support Recursive Subquery Factoring (see APC's answer), one very low-tech alternative is to encode the hierarchy into a separate key. Of course this will only work if you can control the table update process and rewrite the key following parent updates.

For example:

FLAT_HIER_ID  NODE_ID   PARENT_NODE_ID
~N1           N1
~N1~N2        N2        N1
~N1~N2~N3     N3        N2
~N1~N2~N4     N4        N2

Not very elegant, but it gives you a route for querying.

Another (probably better) option is to use the "Nested Set Model" method. This is the method used in Fogbugz for handling subcase querying in MSSQL, MySQL, and MS Access.

The model is explained at:

http://www.developersdex.com/gurus/articles/112.asp

The use of the method in Fogbugz is described at:

http://www.fogcreek.com/FogBugz/blog/post/Subcases-and-Hierarchy.aspx