query that provides hierarchy

Analytics
Enthusiast

query that provides hierarchy

Can someone give me a pointer how to write a query that provides a sorted, indented organization hierarchy given a table with child org id and parent org id. Example of sorting below; I tried to show spaces in the indents, but this forum deletes leading spaces or tabs.

Org 1
Org 1's children
Org 1's grandchildren
etc.
Org 2
Org 2's children
Org 2's grandchildren
etc.
Org 3
Org 3's children
Org 3's grandchildren
etc.

TIA,
Nolan
3 REPLIES
rgs
Enthusiast

Re: query that provides hierarchy

I think if you look at the Teradata SQL documentation under the SELECT statement using the “WITH RECURSIVE” clause they provide that exact example.
Enthusiast

Re: query that provides hierarchy

The only example I find on p 61 lists all subordinates of a given manager at all levels by employee number, without regard to the managers in between. An employee number 100250 doesn't necessarily report to employee number 100249 and doesn't necessarily manage employee 100251.

I was looking for something that would, in effect, assign sequence numbers to all employees sorting them so the lowest level subordiates would be grouped under their managers, those managers (with their subordinates below them) grouped with this peers, etc, up to the top of the hierarchy.

Re: query that provides hierarchy

We are using with recursive to display the metric hierarchy but its not showing up in correct sequence. Plz let us know if you have found any solution.