| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Help using hierarchical query CONNECT BY clause
Okay, this is not for the feint harted and I've written a lot.
Issue: I can't formulate SQL for a hierarchical statement using Oracles
"CONNECT BY" clause
Oracle Version: 10g
Background: Working with an employee hierarchy example for simplicity. The
real system reflects a laboratory sample tracking system, lots of things
happens to one sample, and then lots of things to each of these samples, and
then etc. The employee name is a sample name, the job title is a reagent
batch. An existing (large) system is already place. I require readonly
in-house mining of new features with absolute minimum of changes to existing
schema. The existing system was developed by a number of authors, including
the poster, and uses Oracle hierarchical queries to address many data
queries. Because of the nature and structure of the existing data, it is
also being used here.
Here is the contrived employee hierarchy scenario that represents the more complex system above.
Company Hierarchy
Luke "Vice President", Peter is his boss John "Vice president" Peter is his boss Mary "Managing Director European Operations", Peter is her bossJames "Special Projects Director", Peter is his boss Mark "Sales Director", Luke is his boss
There are many many other people and job titles too. I have included the setup SQL below to help with copy/paste to get the same data for development on your machines.
CREATE TABLE employees(empName VARCHAR2(15),empJobTitle VARCHAR2(40),empID NUMBER PRIMARY KEY,bossID NUMBER);
INSERT INTO employees VALUES('Peter','President',1,NULL);
INSERT INTO employees VALUES('Luke','Vice President',2,1);
INSERT INTO employees VALUES('John','Vice President',3,1);
INSERT INTO employees VALUES('Mary','Managing Director European
Operations',4,1);
INSERT INTO employees VALUES('James','Special Projects Director',5,1);
INSERT INTO employees VALUES('Mark','Sales Director',6,2);
INSERT INTO employees VALUES('Tim','Marketing Director',7,2);
INSERT INTO employees VALUES('Johan','HR Director',8,2);
INSERT INTO employees VALUES('Mike','Sales Director',9,3);
INSERT INTO employees VALUES('Stephen','Sales Director',10,4);
INSERT INTO employees VALUES('Harry','HR Assistant',11,8);
INSERT INTO employees VALUES('Michelle','HR Assistant',12,8);
What I want is SQL to retrieve the names of the people in the organisation hierarchy that have as a job title "Sales Director" however their boss must have a job title of "Vice President" and their boss must have the job title of "President". And I want these job titles to be configurable too, so that I can change them on-the-fly and perform different queries.
So my question is what SQL do I need to write to retrieve the names of
employees that work for people with these job titles in this order
"President", "Vice President", "Sales Director", IE ONLY THE EMPLOYEES Mike
and Mark in the example above (BUT NOT Stephen, his bosses job title is
"Managing Director European Operations" and not "Vice President").
I'm not just asking getting others to do the hard work without thinking about this significantly myself. Actually I have been trying without luck myself for over a week before desperately asking for some help in this newsgroup. Here is the bones of what I have coded.
CREATE TYPE empJobTitles AS TABLE OF VARCHAR2(40); /
CREATE OR REPLACE PROCEDURE printEmployees(jobTitles IN empJobTitles) IS
CURSOR myCur(jt IN empJobTitles) IS
SELECT empName,empJobTitle,LEVEL AS lvl
FROM employees
CONNECT BY PRIOR empID=bossID
DBMS_OUTPUT.PUT_LINE('==================');
OPEN myCur(jobTitles);
FETCH myCur
INTO myRow;
EXIT WHEN myCur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RPAD('-',myRow.lvl*2) || myRow.empName || ' (' ||
myRow.empJobTitle || ')');
END LOOP;
END;
CLOSE myCur;
END;
/
SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.ENABLE(100000);
EXEC printEmployees(empJobTitles('President','Vice President','Sales
Director'));
Employee Hierarchy
This query prints out all employees in the hierarchy as show above with a
bit of left padding to show the hierarchy, it doesn't take into account the
"President", "VP", and "Sales Director" limitations passed in as the
procedure argument. But I've put the output here so that someone might have
a brainwave on how the procedure "printEmployees" could be modified to take
this into account.
Also the following doesn't answer the question either but I feel I am on the right track. Despite the obvious bug, I've included it more to show some of the not necessarily familiar syntax in a syntactly correct snippet. Again it's included so someone more able than I can pick it up, extend, and hopefully arrive at a solution.
CREATE OR REPLACE PROCEDURE printEmployees(jobTitles IN empJobTitles) IS
CURSOR myCur(jt IN empJobTitles) IS
SELECT empName
FROM employees t1,
THE (
SELECT CAST(jt AS empJobTitles)
FROM DUAL
) t2
CONNECT BY PRIOR empID=bossID
AND t1.empJobTitle=t2.COLUMN_VALUE
START WITH bossID IS NULL;
FETCH myCur
INTO myRow;
EXIT WHEN myCur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(myRow.empName);
END LOOP;
Thank you in advance. Any queries please post to the newsgroup. Hex
Please don't bother to reply with RTFM, this is trivial, this is a waste of bandwidth, redesign your problem and I might help you, this was posted last week etc. I don't find this helpful and it also leads others reading the current newsgroup thread to believe my post has been answered when it has not. If what I am saying is trivial etc, if possible please provide a URL for others with similar problems to follow, or alternatively if relevant give a code or pseudocode example. Remember an expert was once too a novice, I am posting for help because perhaps this isn't my field of expertise, or maybe it even is and I things have become clouded, or maybe I even need help from someone with greater knowledge.
If I am replying to an existing post in a newsgroup:
Please note the posting is provided in good faith by an IT professional in his own time from home. I have good general knowledge of various technologies and considerable expertise in others. Assistance given is my personal view and any technical implementation relates to my interpretation, expertise, and the current discussion thread. My opinions are my own and do not reflect on any employer or inside knowledge of a specific organisation. Any information provided is provided "as is" with no warranty implied nor liability accepted.
I will not respond to flaming, unnecessarily terse, or
ad hominen attacks. If possible please direct all questions in
the newsgroup for others to benefit. In the event you wish to
contact me privately and directly, remove the obvious part
from my e-mail address. In the event I choose to disclose
my "real name" to you (other than my nom de plume
"Hexathioorthooxalate"), please treat this information as
confidential and unless explicitly authorised in writing, not
disclose this information to others.
Received on Tue Feb 01 2005 - 12:38:37 CST
 |
 |