Re: Fetching from a hierarchical data structure?

From: Ian Ledzion <ian.ledzion_at_lgxbow.com>
Date: Tue, 10 Apr 2001 14:20:31 +0200
Message-ID: <9autqb$bge$1@rex.ip-plus.net>

If you want this kind of structure, use CONNECT BY. In your table you need a PK, and the reference of the parent. Top level parent (A)'s parent is NULL. Try running this script then the query. Note that the results are not sorted, so you have to create a function, integrate it into the result set and sort according to that. Happy coding!

/*Begin Script*/
CREATE TABLE HIERARCHY
(

   HR_IDENTIFIER                 NUMBER(3) NOT NULL,
   HR_PARENT                     NUMBER(3),
   HR_COMMENT                    VARCHAR2(30))

/

ALTER TABLE HIERARCHY
 ADD CONSTRAINT HR_PK
 PRIMARY KEY
 ( HR_IDENTIFIER
 )
/

DELETE FROM hierarchy;

INSERT INTO hierarchy  VALUES (0, NULL, 'Parent');
INSERT INTO hierarchy  VALUES (1, 0, 'Level 1 Node 1');
INSERT INTO hierarchy  VALUES (2, 0, 'Level 1 Node 2');
INSERT INTO hierarchy  VALUES (3, 1, 'Level 2 Node 1');
INSERT INTO hierarchy  VALUES (4, 1, 'Level 2 Node 2');
INSERT INTO hierarchy  VALUES (5, 3, 'Level 3 Node 1');

COMMIT;
/*End Script*/

/*Begin Query*/
SELECT ROWNUM, LEVEL, a.hr_identifier, a.hr_parent, a.hr_comment FROM hierarchy a
START WITH a.hr_identifier = 0
CONNECT BY PRIOR a.hr_identifier = a.hr_parent /*End Query*/

"FT" <ft_at_ep-soft.dk> wrote in message
news:hcAA6.127$X22.1787_at_news.get2net.dk...
> Hi
>
> An alternative approach would be to create a "Key" column. This column
 holds
> a key string made up of Subkey from each level in the hierarchy. IE:
>
> A
> / \
> 1 2
> / \
> 3
>
> "A" SubKey = 00001
> "1" SubKey = 00001
> "2" SubKey = 00002
> "3" SubKey = 00001
>
> Now when i walk up the hirarchy the complete key for "3" will be:
> 000010000100001
> This value is put into the Key column of this record. Now to find all
 items
> below "1" I can simply say:
> Select * From Table Where Key LIKE '000010001*';
> The tricky part, is of cource to generate the subkeys and the keys.
> Hope this helps. (If i misunderstood your question, please bear with me,
> english is not my language).
>
> Best Regards
> FT
>
> "Christoph Pfrommer" <Christoph.Pfrommer_at_oracle.com> wrote in message
> news:3AD2267E.4219C2EA_at_oracle.com...
> >
> >
> > Maury Markowitz wrote:
> >
> > > "Christoph Pfrommer" <Christoph.Pfrommer_at_oracle.com> wrote in message
> > > news:3AD14E79.7CD7461B_at_oracle.com...
> > > > A few words on typical DW architecture: The well known star schema
 consists of a
> > > > large fact table (as in your application) and some lookup tables,
 one
 for
 each
> > > > (hierarchical) dimension. Since lookup tables are having a different
> > > > representation of hierarchies - one column for each level - you are
 coding
 your
> > > > SQL with some joins
> > >
> > > Now this certainly sounds like a good idea in performance terms, but
> > > doesn't this limit the depth of the hierarchy?
> > >
> > > Maury
> >
> > Maury,
> > that's true. (Even if you add one or two extra levels your argument
 remains
> > valid).
> >
> > However, data warehouses are mostly dealing with static or slowly
 changing
> > dimensions and hierarchies. ('Cause people want to analyze their data
 with
> > well-known structures.) If you talk to the right people, you should know
 of
> > those changes in advance.
> >
> > And if you want to go to extremes (in terms of coding): You can write
 load
> > procedures in Oracle PL/SQL, that add new columns dynamically, e.g.
 statements
> > like:
> > execute immediate ('alter table my_table add column level' || i);
> > where i stands for the depth of your tree.
> >
> > Hope this helps.
> > Christoph.
> >
>
>
Received on Tue Apr 10 2001 - 07:20:31 CDT