Re: leaf rows

From: Hexathioorthooxalate <ruler_at_removemetoemail.clara.co.uk>
Date: Wed, 2 Mar 2005 11:07:14 -0000
Message-Id: <1109761634.48305.0@dyke.uk.clara.net>

>
> 9.0.1, sorry, I forgot to specify that. So, as I guessed, I have to use a
> method based upon my data instead of a standard Oracle function for
> determining whether the row is a leaf or not, isn't it?

Yep. That's right. If your data permits, you could store additional info in the source row indicating whether it is a leaf or not (I say if you can, don't know your data, whether there are multiple roots/trees etc). Alternatively, if this is just an ad hoc query or you know performance wouldn't be an issue, you could implement something like (untested):

CREATE TABLE tblYourTable(yourData1 NUMBER,yourData2 NUMBER,yourData3 DATE,individualRowID NUMBER,parentID);
INSERT INTO tblYourTable ....... (n times) CREATE INDEX ..... (as necessary)

then perform the hierarchical query like [the column alias "is_leaf" will contain 1 or 0 as to whether the row is a leaf (1) or not (0)].

CREATE VIEW vwYourName AS
 SELECT yourData1,

               yourData2,
               yourData3
       individualRowID,
       parentID,
       (
         SELECT DECODE(COUNT(*),1,1,0)
          FROM tblYourTable
             CONNECT BY PRIOR individualRowID=parentID
              START WITH individualRowID=t1.indivdualRowID
       ) is_leaf

   FROM tblyourTable t1
    CONNECT BY PRIOR indivdualRowID=parentID      START WITH .... Hex.

"Cris Carampa" <cris119_at_operamail.com> wrote in message news:422596d3$0$32340$5fc30a8_at_news.tiscali.it...
> Hexathioorthooxalate wrote:
>
>> If you are using 10g, there is the pseudorow CONNECT_BY_ISLEAF. If you
>> are using 9i, you will have to determine whether the row is a leaf or not
>> yourself. What version of Oracle are you using?
>
> 9.0.1, sorry, I forgot to specify that. So, as I guessed, I have to use a
> method based upon my data instead of a standard Oracle function for
> determining whether the row is a leaf or not, isn't it?
>
> 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 - 05:07:14 CST