Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: learning CONNECT BY structure

    I have a simplified example of a system we're developing, where we're tracking the serial order of jobs executed in a scheduling tool we've purchased. My example goes like this:

    Code:
    SQL> create table job
      2  (job_name varchar2(10),
      3   precursor_job_name varchar2(10));
    
    Table created.
    
    SQL> insert into job values ('job1',null);
    
    1 row created.
    
    SQL> insert into job values ('job2','job1');
    
    1 row created.
    
    SQL> insert into job values ('job3','job2');
    
    1 row created.
    
    SQL> select job_name, precursor_job_name
      2  from job
      3* connect by prior job_name = precursor_job_name
    
    JOB_NAME   PRECURSOR_
    ---------- ----------
    job2       job1
    job3       job2
    job3       job2
    job1
    job2       job1
    job3       job2
    
    6 rows selected.
    I guess I was expecting
    Code:
    JOB_NAME   PRECURSOR_
    ---------- ----------
    job1
    job2       job1
    job3       job2
    
    3 rows selected.
    Why didn't that bear out in the results?

    Thanks,
    Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Further, job3 shows 3 entries, each with a different LEVEL value?
    Code:
    SQL> select job_name, precursor_job_name, LEVEL
      2  from job
      3* connect by prior job_name = precursor_job_name
    SQL> /
    
    JOB_NAME   PRECURSOR_      LEVEL
    ---------- ---------- ----------
    job2       job1                1
    job3       job2                2
    job3       job2                1
    job1                           1
    job2       job1                2
    job3       job2                3
    
    6 rows selected.
    hmmm...
    -cf

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think you need to use the start with clause so it knows where to start the tree.

    http://www.adp-gmbh.ch/ora/sql/connect_by.html

    Alan

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    In addition to the link AlanP gave, see Note:29704.1 on Metatlink

    Perhaps this is what you need:


    Code:
    select job_name, precursor_job_name
    from job
    connect by prior job_name = precursor_job_name
    start with precursor_job_name is null

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    SkyWriter, that's exactly it - it was also provided in AlanP's linked page. Thanks to both of you.

    I'm having a tough time interpreting the command START WITH in this context. Does anyone have a good explanation of that concept, and why it helps deliver the data in the format I was expecting? The Oracle docs don't do a good job here.

    -Chuck

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It just to tell Oracle where the top of your tree starts i.e. your top node

    Alan

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    Lets say you work in a company and each employee has a manager stored in the same table ala the Oracle tables that ship with the db.

    Code:
    Emp ID   Mgr Id
    1           
    2           1
    3           1
    4           3
    5           4
    6           4
    7           5
    If you wanted to find the whole chain from employee 7, you can put that in your start with clause:
    Code:
    start with employee_id = 7
    Not sure if that made any sense on top of what Alan said - you can start anywhere in the tree, and that start with clauses determines where you start. Where you put the prior in the connect by clause determines which direction you go.
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •