Help needed in SQL Query for Hierarchy

I would like to seek your expert opinions on the following problem of mine:

I have a table to store employees

Here is the partial structure of the table

EMPID
EMP_POS
DATE
MGMT
BOSS_POS The data looks like this:

EMPLID	EMP_POS	DATE		FLAG	BOSS_POS
z001	p009	01/01/2000	N	p008
z001	p019	03/11/1999	N	p031
z001	p029	11/30/1998	N	p085

y001	p008	06/13/2001	N	p007
y001	p031	08/23/2000	N	p022
y001	p054	01/08/1998	N	p045
	
x001	p007	09/23/2000	Y	p006

w001	p006	03/11/1998	N	p005

It is evident that this stores the positions that an employee is currently in and has been. For example, employee z001 is in position p009 as that is the most recent record and has previously been in positions p019 and p029.

This is true for all employees with the number of positions varying from 1 to many.

I am working on a query that returns the EMPLID that has the flag set to Y for a given input EMPLID ie. For input EMPLID = 'z001', the query should return EMPLID = 'x001' even when 'w001' is the Boss of 'x001' so is the Boss of 'z001' The level at which the FLAG is set is not known so the query has to traverse till it finds the record with FLAG set or return the record from the result after traversing the whole tree right up to the top.

In writing this query I have to take the record of the employee that has the most recent date.

Attempted Solution
I thought of creating a view that would return me the current position of each employee and then using 'CONNECT BY PRIOR' to traverse the hierarchy. View
CREATE VIEW the_view AS
SELECT A.EMP_POS, A.EMPLID
FROM the_table A
WHERE A.EMP_POS <> ' '
AND A.DATE = (
SELECT MAX(C.DATE)
FROM the_table C
WHERE C.EMPLID = A.EMPLID
AND C.DATE <= SYSDATE
)

select EMPLID, EMP_POS, FLAG

         from the_view
         connect by prior EMP_POS = BOSS_POS
         start with EMP_POS = 'z001'
         

But then CONNECT BY PRIOR does not accept sub-queries Secondly, I would have to use the above statement as an inline view to filter the result to return the record where FLAG = 'Y'

What could be the most efficient way of doing this?

Thanking you in advance for your suggestions. Received on Sat Sep 13 2003 - 00:48:36 CEST