473,322 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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.
Jul 19 '05 #1
1 6508
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_pos
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).
Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: knoak | last post by:
Hi there, I'm building a website about animals. There is a mySQL DB behind it. There are 2 tables: 1 - species 2 - animals Species could be: birds, fishes etc. Animals would be: hawk, eagle...
0
by: Mike N. | last post by:
Hello to all: First let me apologize for the length of this question, I've made an attempt to include as much information as is needed to help with the question. I am having problems putting...
1
by: joey | last post by:
I have a SQL query where I get the count of something, but what I really want is a yes/no (boolean) answer. Here is my query: SELECT COUNT(*) AS COUNT FROM hd_entries,hierarchy WHERE IFNULL...
3
by: Brent Taylor via AccessMonster.com | last post by:
I am working on a MS Access database to track the sales of a 3 Tier Multi- level Marketing system. I have created a table to track a new RepID and a SponsorID. I have created a query that will...
0
by: Henry | last post by:
I have a dataset dsMain in which I have two tables The table period has columns like period_id, name, root_org_id.... The table organization has org_id, name, parent_id, hier_path, level,...
53
by: Hexman | last post by:
Hello All, I'd like your comments on the code below. The sub does exactly what I want it to do but I don't feel that it is solid as all. It seems like I'm using some VB6 code, .Net2003 code,...
31
by: Mark Dufour | last post by:
Hi all, I have recently released version 0.0.20 and 0.0.21 of Shed Skin, an optimizing Python-to-C++ compiler. Shed Skin allows for translation of pure (unmodified), implicitly statically typed...
1
by: shilsum | last post by:
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
3
by: IAuditor | last post by:
Access 2003, XP - I have a multi-table query that is losing data. 1 table is a lookup (Hierarchy), and the other 3 are virtual duplicates (all with a one-to-many relationship with the lookup) except...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.