Standard alternative to CONNECT BY?
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.
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 N2Not 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