Re: Proper siblings sorting in nested sets model

"Pavel Schevaev" <pacha_shevaev_at_mail.ru> wrote in message news:320b3b2.0404090545.692729f1_at_posting.google.com...
> Nested sets are just great and i've been using it for quite a while
> already.
> The only inconvenience that popped up lately was that it seems like
> it's impossible to order node siblings by some arbitrary parameter,
> say, alphabetically, in sigle query without any vendor-specific stuff.

You have to build Materialized Path with the column of your choice and sort by it. For employee name you'll get strings like this:

KING
KING.BLAKE
KING.BLAKE.ALLEN You can build such strings, though, only if your database supports LIST aggregate function. Google any of the following: "Litwin LIST aggregate", "iAnywhere LIST". Alternatively, if your database supports user-defined aggregates, then you can code this aggregate function. Yet another alternative is to program LIST aggregate function on database application level if your database supports object extensions/collections. Be careful, since string concatenation is not commutative, so you have to warry about the order.

Assuming that you have LIST aggregate, then find path from the root to the leaf in the subquery within the "select" clause (does your database support subqueries within the "select" clause?) for each leaf and aggregate those node names with the LIST function. Received on Fri Apr 09 2004 - 18:39:51 CEST