Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help using hierarchical query CONNECT BY clause

Re: Help using hierarchical query CONNECT BY clause

From: NONAME <nospace_at_company.org>
Date: Tue, 1 Feb 2005 17:55:38 -0500
Message-ID: <0OTLd.67204$Mx.1514525@wagner.videotron.net>


I am sure there are better ways to do this. You might want to ASKTOM as I am sure he will have some nifty trick to try. However, the following might get you started.

You can play around with the inline view so that you are not passing data around that you don't need I only did it that way becuse I built the query piece by piece.

select empid,empJobTitle, level CHainlevel, bossId   --sys_connect_by_path(empJobTitle,'/') from employees
start with empid in (
select empid
  from

       (
         select b.*,

(select empJobTitle from employees where empid =
b.bossbossid) bossboss from
(
select a.*, (select empJobTitle from employees where empid = a.bossid) boss, (select bossid from employees where empid = a.bossid) bossbossid from employees a where empJobTitle = 'Sales Director' ) b where boss = 'Vice President' )

 where bossboss = 'President'
)
connect by prior bossId = empid

"Hexathioorthooxalate" <ruler_at_removemetoemail.clara.co.uk> wrote in message news:1107283109.12788.0_at_sabbath.news.uk.clara.net...

> 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);
>
>
> 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
>    START WITH bossID IS NULL;
>  myRow myCur%ROWTYPE;
> BEGIN
>   DBMS_OUTPUT.PUT_LINE('Employee Hierarchy');
>   DBMS_OUTPUT.PUT_LINE('==================');
>   OPEN myCur(jobTitles);
>    BEGIN
>     LOOP
>      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
> ==================
> - Peter (President)
> -   Luke (Vice President)
> -     Mark (Sales Director)
> -     Tim (Marketing Director)
> -     Johan (HR Director)
> -       Harry (HR Assistant)
> -       Michelle (HR Assistant)
> -   John (Vice President)
> -     Mike (Sales Director)
> -   Mary (Managing Director European Operations)
> -     Stephen (Sales Director)
> -   James (Special Projects Director)
>
> 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;
>  myRow myCur%ROWTYPE;
> BEGIN
>   OPEN myCur(jobTitles);
>    BEGIN
>     LOOP
>      FETCH myCur
>        INTO myRow;
>       EXIT WHEN myCur%NOTFOUND;
>      DBMS_OUTPUT.PUT_LINE(myRow.empName);
>     END LOOP;
>    END;
>   CLOSE myCur;
> END;
> /
>
> Thank you in advance. Any queries please post to the newsgroup.
> Hex
>
>
>
> ======================================
> Disclaimer:
> If I am posting a question in a newsgroup:
>    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 - 16:55:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US