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
Hirerachy table logic help needed
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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]
[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]
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
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.
What RDBMS are you using? Oracle, for example, has a "CONNECT BY" syntax that can "flatten" hierarchical relationships in the manner you require.
J.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
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"]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]
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]
[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]
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
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 ...
If you want to test it for an individual employee ...
Presumably the employee is CHILD and the supervisor is PARENT (unless you're a socialist )
J.
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;
Code: Select all
SELECT DISTINCT CHILD, PARENT
FROM {table}
START WITH CHILD = '{employee_key}'
CONNECT BY NOCYCLE PRIOR CHILD = PARENT
ORDER BY CHILD;
J.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>