| 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 -> Re: Help using hierarchical query CONNECT BY clause
Thanks for your help everyone. What is important to the query is that we can
change things on the fly (ie different level or number of job titles etc).
I've got it working with a variation of what I originally posted, and ideas
from the people that replied to my post. I've pasted it in here verbatim in
the even that someone else finds it useful.
Regards
Hex
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);
CREATE OR REPLACE TYPE empJobTitles AS VARRAY(50) OF VARCHAR2(40); /
CREATE OR REPLACE FUNCTION countJobTitles(jobTitles IN empJobTitles) RETURN
NUMBER IS
BEGIN
RETURN jobTitles.COUNT;
END;
/
CREATE OR REPLACE FUNCTION getJobTitleAtLevel(jobTitles IN empJobTitles,lvl
IN NUMBER) RETURN VARCHAR2 IS
fResult VARCHAR2(40);
BEGIN
IF (lvl>0) AND (lvl<=countJobTitles(jobTitles)) THEN
fResult:=jobTitles(lvl);
ELSE
fResult:=NULL;
END IF;
RETURN fResult;
END;
/
CREATE OR REPLACE PROCEDURE printEmployees(jobTitles IN empJobTitles) IS
CURSOR myCur IS
SELECT empName
FROM employees t1
WHERE LEVEL=countJobTitles(jobTitles)
CONNECT BY PRIOR empID=bossID
AND t1.empJobTitle=getJobTitleAtLevel(jobTitles,LEVEL)
AND LEVEL<=countJobTitles(jobTitles)
START WITH bossID IS NULL;
FETCH myCur
INTO myRow;
EXIT WHEN myCur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(myRow.empName);
END LOOP;
SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.ENABLE(100000);
--Should print Mark & Mike
EXEC printEmployees(empJobTitles('President','Vice President','Sales
Director'));
--Should print Stephen
EXEC printEmployees(empJobTitles('President','Managing Director European
Operations','Sales Director'));
--Should not display any employees
EXEC printEmployees(empJobTitles('President','Managing Director European
Operations','PA'));
--And could even call printEmployees with 4 or 5 or 6 levels of job title!!
"Andy Hassall" <andy_at_andyh.co.uk> wrote in message
news:7luvv054gjk4to4fu8in8vn1jkddjruaq5_at_4ax.com...
> On Tue, 1 Feb 2005 18:38:37 -0000, "Hexathioorthooxalate"
> <ruler_at_removemetoemail.clara.co.uk> wrote:
>
>>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
>>=========================
>>Peter "President", no boss
>>Luke "Vice President", Peter is his boss
>>John "Vice president" Peter is his boss
>>Mary "Managing Director European Operations", Peter is her boss
>>James "Special Projects Director", Peter is his boss
>>Mark "Sales Director", Luke is his boss
>>Tim "Marketing Director", Luke is his boss
>>Johan "HR Director", Luke is his boss
>>Mike "Sales Director", John is his boss
>>Stephen "Sales Director", Mary is her boss
>>Harry "HR Assistant", Johan is his boss
>>Michelle "HR Assistant", Johan is her 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);
>
> Yay, sample data.
>
>>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").
>
> OK, step one: find those people whose job title is VP and whos boss is
> Pres:
>
> SQL> select e2.*
> 2 from employees e1
> 3 join employees e2
> 4 on (e2.bossid = e1.empid)
> 5 where e1.empjobtitle = 'President'
> 6 and e2.empjobtitle = 'Vice President';
>
> EMPNAME EMPJOBTITLE EMPID
> BOSSID
> --------------- ---------------------------------------- ---------- ----------
> Luke Vice President 2
> 1
> John Vice President 3
> 1
>
> Step two: find reports of those VPs whose job title is Sales Director:
>
> SQL> select *
> 2 from employees
> 3 where empjobtitle = 'Sales Director'
> 4 connect by prior empid = bossid
> 5 start with empid in (
> 6 select e2.empid
> 7 from employees e1
> 8 join employees e2
> 9 on (e2.bossid = e1.empid)
> 10 where e1.empjobtitle = 'President'
> 11 and e2.empjobtitle = 'Vice President'
> 12 );
>
> EMPNAME EMPJOBTITLE EMPID
> BOSSID
> --------------- ---------------------------------------- ---------- ----------
> Mark Sales Director 6
> 2
> Mike Sales Director 9
> 3
>
> Since it only goes down one level in your data then the connect by is
> superfluous here, you could have just done a three-way join.
>
> SQL> select e3.*
> 2 from employees e1
> 3 join employees e2
> 4 on (e2.bossid = e1.empid)
> 5 join employees e3
> 6 on (e3.bossid = e2.empid)
> 7 where e1.empjobtitle = 'President'
> 8 and e2.empjobtitle = 'Vice President'
> 9 and e3.empjobtitle = 'Sales Director';
>
> EMPNAME EMPJOBTITLE EMPID
> BOSSID
> --------------- ---------------------------------------- ---------- ----------
> Mark Sales Director 6
> 2
> Mike Sales Director 9
> 3
>
> If you had more Sales Directors further down the hierarchy, or Sales
> Directors
> had people reporting to them then connect by might come into actual use.
>
> e.g.
>
> SQL> INSERT INTO employees VALUES('Postlethwaite','Sales Director',13,6);
>
> 1 row inserted
>
> SQL> select lpad('-',level-1,'-')||e.empname empname, e.empjobtitle,
> e.empid
> 2 from employees e
> 3 where empjobtitle = 'Sales Director'
> 4 connect by prior empid = bossid
> 5 start with empid in (
> 6 select e2.empid
> 7 from employees e1
> 8 join employees e2
> 9 on (e2.bossid = e1.empid)
> 10 where e1.empjobtitle = 'President'
> 11 and e2.empjobtitle = 'Vice President'
> 12 );
>
> EMPNAME EMPJOBTITLE EMPID
> -------------------- ---------------------------------------- ----------
> -Mark Sales Director 6
> --Postlethwaite Sales Director 13
> -Mike Sales Director 9
>
> --
> Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Received on Wed Feb 02 2005 - 12:17:58 CST
 |
 |