Page 1 of 1
Hirerachy table logic help needed
Posted: Mon Apr 07, 2008 3:13 pm
by 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
Posted: Mon Apr 07, 2008 3:32 pm
by 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).
Posted: Mon Apr 07, 2008 10:23 pm
by 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]
Posted: Mon Apr 07, 2008 11:15 pm
by 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.
Posted: Tue Apr 08, 2008 7:58 am
by 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]
Posted: Tue Apr 08, 2008 12:27 pm
by 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]
Posted: Tue Apr 08, 2008 8:28 pm
by 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.