How to find the find the 3 highest designation for a particular employee.

Database

How to find the find the 3 highest designation for a particular employee.

Hi,

I am working on certain requirement, where we need to so all the employees directly or indirectly reporting to him. The very 1st step, i have created a hierarchical structure displaying the employee to till its highest level(in our case VP is the highest level).

The table structure looks like

employee_no employee_nm L1_mgr L2_mgr L3_mgr L4_mgr L5_mgr l6_mgr L7_mgr

in this case the last not null column will be the VP, next not null column is GM followed by FM and MGR1, MGR2 like this.

so i need to populate the the result in flat table like below

employee_no employee_nm VP GM FM MGR3 MGR2 MGR1.

Can anybody gave some approach to write SQL which will derive its VP ,GM ,FM and so on above listed values.

Thanks
Sanjib
1 REPLY
Enthusiast

Re: How to find the find the 3 highest designation for a particular employee.

This is usually the kind of problem that Recursive SQL was made for, but with your already flattened structure, that becomes no longer works. I guess you'd need to use a CASE statement.

Something like:

CASE
WHEN L7_mgr IS NOT NULL THEN L7_mgr
WHEN L6_mgr IS NOT NULL THEN L6_mgr
WHEN L5_mgr IS NOT NULL THEN L5_mgr
WHEN L4_mgr IS NOT NULL THEN L4_mgr
WHEN L3_mgr IS NOT NULL THEN L3_mgr
WHEN L2_mgr IS NOT NULL THEN L2_mgr
WHEN L1_mgr IS NOT NULL THEN L1_mgr
END AS VP,
CASE
WHEN L7_mgr IS NOT NULL THEN L6_mgr
WHEN L6_mgr IS NOT NULL THEN L5_mgr
WHEN L5_mgr IS NOT NULL THEN L4_mgr
...
END AS GM
,
...