Re: Hierarchical query

I agree it can be done without the extra column. But my point was that is is not a good design strategy. If you wanted to add a row under (lets say) Salary, what would be the id/number you would assign to it? If the table is a static table, it is not much of an issue, but if it is going to be updated often, then an extra column is needed.

kirtan.

Michel Cadot wrote:
> "RT" <robbyt_at_unocal.com> a écrit dans le message de
> news:1111384975.161518.161250_at_f14g2000cwb.googlegroups.com...
> | I got a table with the following format:
> |
> | insert into tab1 (1,'E1');
> | insert into tab1 (2,'E2');
> | insert into tab1 (3,'E3');
> | insert into tab1 (4,'Salary');
> | insert into tab1 (5,'E4');
> | insert into tab1 (6,'E5');
> | insert into tab1 (7,'E6');
> | insert into tab1 (8,'E7');
> | insert into tab1 (9,'Benefit');
> |
> | and I want to ouput the following:
> |
> | E1, Salary
> | E2, Salary
> | E3, Salary
> | E4, Benefit
> | E5, Benefit
> | E6, Benefit
> | E7, Benefit
> |
> | Can anybody please help me with the query.
> | Many thanks in advance.
> |
>
> 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> commit;
> SQL> select * from tab1;
>
> COL1 COL2
> ---------- ----------
> 1 E1
> 2 E2
> 3 E3
> 4 Salary
> 5 E4
> 6 E5
> 7 E6
> 8 E7
> 9 Benefit
>
> 9 rows selected.
>
> Old style
> =========
>
> SQL> select b.col2, a.col2
> 2 from tab1 a, tab1 b
> 3 where a.col2 not like 'E_'
> 4 and b.col2 like 'E_'
> 5 and b.col1 < a.col1
> 6 and not exists (select 1 from tab1 c
> 7 where c.col1>b.col1 and c.col1<a.col1 and c.col2 not
like 'E_')
> 8 order by 1
> 9 /
>
> COL2 COL2
> ---------- ----------
> E1 Salary
> E2 Salary
> E3 Salary
> E4 Benefit
> E5 Benefit
> E6 Benefit
> E7 Benefit
>
> 7 rows selected.
>
> With analytic functions
> =======================
> (faster but harder to read)
>
> SQL> select col2, substr(ocol2,5) col2
> 2 from ( select col1, col2,
> 3 min(rn) over (order by col1 desc) ocol2
> 4 from ( select col1, col2,
> 5 case when col2 not like 'E_' then to_char(rn,'999')||col2
> 6 else null end rn
> 7 from ( select col1, col2,
> 8 row_number() over (order by col1) rn
> 9 from tab1 )
> 10 ) )
> 11 where col2 like 'E_'
> 12 order by col1
> 13 /
>
> COL2 COL2
> ---------- ----------
> E1 Salary
> E2 Salary
> E3 Salary
> E4 Benefit
> E5 Benefit
> E6 Benefit
> E7 Benefit
>
> 7 rows selected.
>
> Regards
> Michel Cadot
Received on Tue Mar 22 2005 - 00:06:29 CST