i have a situation where i will be passing object to get the parent object , the challenge is there are 3 parents to that object , for example if i have object 4 and want to get object 3 it should get object 3 , if want to get parent object 2 it should get object 2 for object 4. For example , i will pass the child object along with level of parent then it should brng the right parent in hierarchy according to level of parent ,
[code]
create table test_object (mch_code varchar2(25),sup_mch_code varchar2(25), mch_code_level varchar2(20))
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA-AAA-AAA-AA','AAA-AAA-AAA','L:5');
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA-AAA-AAA','AAA-AAA','L:4');
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA-AAA','AAA','L:3');
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('AAA','A','L:2');
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB-BBB-BBB-BB','BBB-BBB-BBB','L:5');
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB-BBB-BBB','BBB-BBB','L:4');
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB-BBB','BBB','L:3');
insert into test_object (mch_code,sup_mch_code , mch_code_level) values ('BBB','B','L:2');
--get ancestor object
select get_ancestor_object('AAA-AAA-AAA','L:2') from dual;
--first is the object , second parameter is the level of parent that i want.
should return 'AAA'
select get_ancestor_object('AAA-AAA-AAA-AA,'L:3') from dual;
should return 'AAA-AAA'
[/CODE]