Re: Hierarchical query

Thanks for the info.

Another question, it turns out that there is another table that holds the hierarchy (to cater for additional values inserted into the table) as follows:

SQL > create table tab1 (col1 number, col2 varchar2(10));
SQL > insert into tab1 values (1,'E1');
SQL > insert into tab1 values (2,'E2');
SQL > insert into tab1 values (3,'E3');
SQL > insert into tab1 values (4,'Salary');
SQL > insert into tab1 values (5,'E4');
SQL > insert into tab1 values (6,'E5');
SQL > insert into tab1 values (7,'E6');
SQL > insert into tab1 values (8,'E7');
SQL > insert into tab1 values (9,'Benefit');
SQL > insert into tab1 values (10,'E8); -- this is new
SQL > insert into tab1 values (11,'E9'); -- this is new

The hierarchy table is setup as follows:

SQL > create table tab2 (col1 number, col_order number);
SQL > insert into tab2 values (1,1);
SQL > insert into tab2 values (2,2);
SQL > insert into tab2 values (3,3);
SQL > insert into tab2 values (10,4);
SQL > insert into tab2 values (4,5);
SQL > insert into tab2 values (5,6);
SQL > insert into tab2 values (6,7);
SQL > insert into tab2 values (7,8);
SQL > insert into tab2 values (8,9);
SQL > insert into tab2 values (11,10);
SQL > insert into tab2 values (9,11);

>From the tab2, this should produce the following:

E1, Salary
E2, Salary
E3, Salary
E8, Salary
E4, Benefit
E5, Benefit
E6, Benefit
E7, Benefit
E9, Benefit

Please help me with the SQL statement under Oracle 7. Many thanks in advance. Received on Fri Apr 08 2005 - 01:49:01 CDT