Hirerachy table logic help needed

[egends]

Have a table which has Employee Number and Supervisor Number.. as given below

Employee Number Sup Number
1 2
2 3
3 4
4 5


The logic for this table is Emp 1 reports to 2.. similarly 2 reports to 3.. so on.

I need to implement a logis in datastage which should for this case return me this output

Employee Number Sup Number
1 2
1 3
1 4
1 5
2 3
2 4
2 5
3 4
3 5
4 5

i.e. it should show for each employee its hierarchy till upper level i.e. say CEO.

Can anyone plz help me implementing this in datastage or any other ideas.

Thanks

[ray.wurlod]

Most databases have a technique in the query language for performing this kind of recursive query. That is the easiest way to do it.

If you insist on using DataStage, you are up for a lookup for each possible level of separation (the number of manages between the lowest-level employee and the CEO).

[egends]

ok thanks for that .. so do you mean that writing a procedure would be a btter option..


[quote="ray.wurlod"]Most databases have a technique in the query language for performing this kind of recursive query. That is the easiest way to do it.

If you insist on using DataStage, you are up for a lookup for each possible level of separation (the number of manages between the lowest-level employee and the CEO).[/quote]

[jhmckeever]

You don't necessarily need to write a procedure. A query could potentially return the data in the form you require.

What RDBMS are you using? Oracle, for example, has a "CONNECT BY" syntax that can "flatten" hierarchical relationships in the manner you require.

J.

[egends]

oh ok.. am not so good in those Connect By would try it and see.. Thanks for the suggestions.. yeah it is Oracle

[quote="jhmckeever"]You don't necessarily need to write a procedure. A query could potentially return the data in the form you require.

What RDBMS are you using? Oracle, for example, has a "CONNECT BY" syntax that can "flatten" hierarchical relationships in the manner you require.

J.[/quote]

[egends]

I tried connect by but gives me path or hierarchy.. but want i really want is the table ouput as i had iterated previously.. can anyone help me with that


[quote="egends"]oh ok.. am not so good in those Connect By would try it and see.. Thanks for the suggestions.. yeah it is Oracle

[quote="jhmckeever"]You don't necessarily need to write a procedure. A query could potentially return the data in the form you require.

What RDBMS are you using? Oracle, for example, has a "CONNECT BY" syntax that can "flatten" hierarchical relationships in the manner you require.

J.[/quote][/quote]

[jhmckeever]

There's a website called www.google.com which is sometimes useful with problems such as this.

However, here's something to get you started ...

Code: Select all

 SELECT DISTINCT CHILD, PARENT
   FROM {table}
CONNECT BY NOCYCLE PRIOR CHILD = PARENT
  ORDER BY CHILD;

If you want to test it for an individual employee ...

Code: Select all

 SELECT DISTINCT CHILD, PARENT
   FROM {table}
  START WITH CHILD = '{employee_key}'
CONNECT BY NOCYCLE PRIOR CHILD = PARENT
  ORDER BY CHILD;

Presumably the employee is CHILD and the supervisor is PARENT (unless you're a socialist )

J.