Re: Trees in SQL

In article <c0d87ec0.0207041041.3954de35_at_posting.google.com>, "--CELKO--" <71062.1056_at_compuserve.com> wrote:

> Nope: SUM() and all other aggregate functions drop the NULLs first, then
> do their math; the exception is COUNT(*), which is a set function and
> not really an aggregate function.
>
>

Aha, I was thinking I should check that before I typed it, but it's good to find out. I've found some db's producing the NULL, though, so they must have nonstandard behavior.

If NULL can't be used, I would just use an indicator column with a boolean value (0-1) and max() it to get the child nodes ANDed together.

i.e, I would create a column is_valid and set parent.is_valid = max(child.is_valid) from the child nodes.

One could also use a "special" value in the count column, like -n, where n is the node count. A case statement could check for a negative sum over the child nodes and turn it back to -n each time, to preserve the "null" value.

The basic issue is to identify the "height" of each node, which is the number of iterations needed to sum from the leaf nodes to a given node N. This quantity is simply the maximum depth to a leaf node from N, i.e. the depth of the subtree rooted at N. Received on Sat Jul 06 2002 - 20:52:50 CEST