Help needed in SQL Query for Hierarchy

[shilsum]

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.

[Russ Bagley]

Re: Help needed in SQL Query for Hierarchy

Try:

select level
,a.emplid
,a.emp_pos
,a.flag
,a.boss_pos
from temp a
where a.date = (select max(c.date)
from temp c
where c.emplid = a.emplid)
and flag = 'Y'
connect by prior boss_pos=emp_po s
start with emplid = 'z001'

It works with the data you supplied (on a 9i database). I don't know
if this is the most efficient way (sub-selects are always pretty
slow).