Re: hierarchical query problem

Cris - from this data (the '-' are your formatting to show the hierarchy in the NNTP post, not real padding in the column ID?) how do we know which data are the root rows and therefore what to use in the average date calculation? If you provided a flag like Y or N or 0 or 1 to say this column is a root, use it to calculate the average date of all data up to the row with the next Y or 1, then (for me anyway) the problem is significantly reduced to just getting the syntax of the appropriate Oracle ANALYTIC FUNCTION right.

Fire through some feedback and I'll see if I can help. Also, same database as before (9i not 10g?)

Hex

"Cris Carampa" <cris119_at_operamail.com> wrote in message news:4225dc8f$0$6300$5fc30a8_at_news.tiscali.it...
>I have a hierarchical query that returns the following output:
>
> id date
> ------------------------------ --------
> 18331 25-01-05
> ---13204 28-10-04
> ------13193 07-10-04
> ---13205 28-10-04
> ------13194 07-10-04
> ---13206 28-10-04
> 18332 25-01-05
> ---13204 28-10-04
> ------13193 07-10-04
> ---13205 28-10-04
> ------13194 07-10-04
> ---13206 28-10-04
> 19521 15-02-05
> ---14798 25-11-04
> ---15228 02-12-04
> ------15223 08-11-04
>
> Please notice that ID is formatted in order to show its hierarchical
> level.
>
> I wish to wrote another query that uses this query as an inline view and
> returns an output like this:
>
> root days
> ----- ----
> 18331 xxx
> 18332 yyy
> 19521 zzz
>
> Where 18331, 18332 and 19521 are the root nodes and "days" are the days
> between the date of the root node and the average date of the leaf nodes.
>
> Any suggestion?
>
> Thank you. Kind regards,
>
> --
> Cris Carampa (cris119_at_operamail.com)
>
> - We're gonna be like three little Fonzies here. And what's Fonzie like?
> - Cool?
>
>
Received on Wed Mar 02 2005 - 10:06:37 CST