i have a hierarchical data stored in a table , i want to get the level 2 object regardless of parameter i pass in the where clause of my select statement, for example i may pass level 3 or level 4 object but it must allways return level 2.
[code]
create table machine_mast(mch_code varchar2(12),mch_name varchar2(200),parent_mch_code varchar2(12),mch_level varchar2(12));
insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('1','machine1',null,'level1');
insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('11','machine2','1','level11');
insert into machine_mast(mch_code,mch_name,parent_mch_code ,mch_level ) values ('111','machine3','11','level111');
--i need a select statement if i pass mch_code = '111'
it must return '11'
[/code]