Hirearchy question

[saikrishna80]

Hi all,
I have a table having columns like

parent child
1 2
1 3
1 4
2 5
2 6
4 7
5 8
7 9

Now input is parent. output must be number of levels from that parent to end of the hierarchy.
for eg : input = 1 output = 3 levels
input = 5 output = 1 level
This is for Informix.

Regards
Sai Krishna

 

[vpshriyan]

Hi Krishna,

Sorry, I am unable to understand your question and requirement. Forgive me if I've misinterpreted your query. I just doubt that you may not be looking for the sql below:

create temp table xtab (parent smallint, child smallint) with no log;

insert into xtab values(1,2);
insert into xtab values(1,3);
insert into xtab values(1,4);
insert into xtab values(2,5);
insert into xtab values(2,6);
insert into xtab values(4,7);
insert into xtab values(5,8);
insert into xtab values(7,9);

select 'input=' input,parent p,'output=' o, count(*) output
from xtab group by 2 order by 2;

Regards,
Shriyan

 

[saikrishna80]

Hi,

1
/ 2 3
/ \ \
4 5 6

This the hierarchy i have maintained in a table.
that is more specifically 1 is the parent and its child are
2 and 3 and the total number of levels is 2. 2 will have 4 and 5 as child and 3 will have 6 as the child.

Now i want a Stored procedure or SQL in Informix so that
if i give the input like below.
Input = 1 (parent) it should list out the hierarchy from 1
i.e parent.
The output should be 2 levels.

Please consider it as urgent and reply soon.

Thanks and warm regards
Sai krishna.

 

[vaishali76]

you can write a recursive function where u can pass the current id, the function will find the child and will call itself again with the child id. but what have to take care is, don't call the self function from inside the cursor, becs cursors will be always global and it will be closed first when u will try to open the same cursor agine.