4. Make the Management Tree

l  Problem

The following is the Employee table of an enterprise, inclusive of the employee ID and name:

EMPNO

ENAME

7902

Ford

7788

Scott

7900

James

7844

Turner

7654

Martin

7521

Ward

7499

Allen

7934

Miller

7876

Adams

7782

Clark

7698

Blake

7566

Jones

7369

Smith

7839

King

The superior-subordinate relationships between employees are given in the table below. For example, the first record indicates that the manager of Employee 7902 is the Employee 7566. The 14th record indicates the Employee 7839 doesn’t have a manager because he is the highest manager.

 

EMPNO

MGR

7902

7566

7788

7566

7900

7698

7844

7698

7654

7698

7521

7698

7499

7698

7934

7782

7876

7788

7782

7839

7698

7839

7566

7839

7369

7902

7839

 

Please return a result set to describe the hierarchical relationships of the data in the table, according to the following structure:

EMP_TREE

King

King – Blake

King – Blake – Allen

King – Clark

King – Clark – Miller

 

l  Tip

General steps: First, select out an employee A, and then find out its line manager B, and then the C that is line manager of B. After finding all these persons, run the loop until reaching the line manager that is the highest leader. Then, jump out of the inner loop, and begin a new loop to search for the managers for the next employee.

Finally, sort the resulting set and you will get the desired result.

 

l  Code 

 

A

B

C

D

 

1

=file("C:\\txt\\Employees2.txt").import@t()

 

 

 

Find the employee information

2

=file("C:\\txt\\Relationships.txt").import@t()

 

 

 

Find the relation between employees

3

=[]

 

 

 

Store the subordinates relation tree having been found

4

for A1

 

 

 

Loop against the employee table

5

 

=A4.EMPNO

 

 

Get the number of the current employee

6

 

=A4.ENAME

 

 

Get name of the current employee

7

 

for

 

 

 

8

 

 

=A2.select(EMPNO==B5).MGR

 

Get the upper class of the current employee

9

 

 

if C8==null

break

If there is no line manager of the current employee, then put an end to the inner loop, and jump to the next employee

10

 

 

=A1.select(EMPNO==C8).ENAME

 

Get name of the line manager of the current employee

11

 

 

>B6=C10+"-"+B6

 

Add the present line manager of the employee to B6

12

 

 

>B5=C8

 

Proceed to select out the line manager’s line manager

13

 

 

next

 

 

14

 

>A3=A3|B6

 

 

Store the found hierarchy tree in the A3

15

=A3.sort()

 

 

 

Sort the cell A3 and display the desired result as required in the problem

 

l  Result