This discussion is archived
5 Replies Latest reply: Nov 25, 2012 11:27 AM by 976068 RSS

Connect by prior by speific order problem.

976068 Newbie
Currently Being Moderated
Oracle: 10.2.0.4

I have a table containing events backup.
i want to list the hierarchy within that backup oreder by the time_stamp for all levels.
create table BCK_EVENTS
(
  bck_backup_id           NUMBER(9) default 0 not null,
  event_id                   NUMBER(15) default 0 not null,
  event_name              NVARCHAR2(100) default ' ' not null
  time_stamp               NUMBER(9) default 0 not null,
  parent_event            NUMBER(15) default 0 not null,
)

add constraint BCK_EVENTS_PK primary key (BCK_EVENT_ID, EVENT_ID); // event_id is not unique can be under one or more backup id's
this is not a tree with one root, there is more than one event in the root level (level 1).

example:
Event Name      Time         level   <- time is numeric but for easier reading.
-------------------- ---------       ---------
 *Event A          10:00         1
   *Event C        10:30         2
     *Event B      11:17         3
 *Event H          12:10         1
     *Event J       12:10         2
     *Event M      12:21         2
 *Event Z          15:33         1
   *Event R        16:56          2
    *Event M      16:57          3
   *Event G        20:20         2
What i tried was :
select  lpad( '*', level*2 ) || event_id,event_name,time_stamp,parent_event,level from bck_events 
     where bck_event_id=100031
     start with parent_event is null
    connect by prior event_id = parent_event
and there are two problems with it.

1. it's not ordered even when i added an Index (parent_event,time_stamp) and try to hint it.
2. it returns loads of multiple rows.

hope it's clear enough. I thank for any help.

Edited by: 973065 on Nov 25, 2012 8:04 AM

Edited by: 973065 on Nov 25, 2012 9:23 AM

Edited by: 973065 on Nov 25, 2012 9:29 AM

Edited by: 973065 on Nov 25, 2012 9:31 AM
  • 1. Re: Connect by prior by speific order problem.
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Connect by prior by speific order problem.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    973065 wrote:
    Oracle: 10.2

    I have a table containing events backup.
    i want to list the hierarchy within that backup oreder by the time_stamp for all levels.
    create table BCK_EVENTS
    (
    bck_backup_id           NUMBER(9) default 0 not null,
    event_id                   NUMBER(15) default 0 not null,
    event_name              NVARCHAR2(100) default ' ' not null
    time_stamp               NUMBER(9) default 0 not null,
    parent_event            NUMBER(15) default 0 not null,
    )
    Thanks for posting the version number and the CREATE TABLE statement. Don't forget to post INSERT statements for your sample data.
    add constraint BCK_EVENTS_PK primary key (BCK_EVENT_ID, EVENT_ID); // event_id is not unique can be under one or more backup id's
    this is not a tree with one root, there is more than one event in the root level (level 1).
    Is it a forest, that is, a set of trees?
    example:
    Event Name      Time         level   <- time is numeric but for easier reading.
    -------------------- ---------       ---------
    *Event A          10:00         1
    *Event C        10:30         2
    *Event B      11:17         3
    *Event H          12:10         1
    *Event J       12:10         2
    *Event M      12:21         2
    *Event Z          15:33         1
    *Event R        16:56          2
    *Event M      16:57          3
    *Event G        20:20         2
    That seems to be a forest, that is, every row has 0 or 1 parent, and no row is its own ancestor.
    What i tried was :

    select lpad( '*', level*2 ) || event_id,event_name,time_stamp,parent_event,level from bck_events
    where bck_event_id=100031
    start with parent_event is null
    connect by prior event_id = parent_event

    and there are two problems with it.

    1. it's not ordered even when i added an Index (parent_event,time_stamp) and try to hint it.
    Depending on your data, you may just need to add
    ORDER SIBLINGS BY  time_stamp
    at the end, after the CONNECT BY clause.

    If you need rows sorted by time_stamp under their roots, but otherwise without regard to the hierarchy, then use CONNECT_BY_ROOT.
    2. it returns loads of multiple rows.
    Again, it depends on your data. I'll bet you need something more in the CONNECT BY clause, but I can't tell what without some sample data and an exxplanation of how you gett the results you posted from that data. The fact that bck_event_id is part of the primary key makes me suspect that maybe bck_event_id needs to be somewhere in the CONNECT BY clause, but that's just a wild guess.
    hope it's clear enough. I thank for any help.
    As mentioned before, see the forum FAQ {message:id=9360002}
  • 3. Re: Connect by prior by speific order problem.
    976068 Newbie
    Currently Being Moderated
    Hey thanks for your help. I'll try to explain myself better now.

    what i'm trying to do is a backup and restore procedure between 2 tables which looks almost identical.
    Table A(original) primary key is (event_id)
    Table B(backup) primary key is (backup_id,event_id)
     original          backup
    ----------       ----------
    Table A    <->    Table B
    The data located in table A is already in a form of a Forest as you pointed out.
    when i backup the data from table A to B i dont mind the order which the rows are backed up because there is no need to create new event_id's.
    but when i restore them from B to A i do need new event_id's cause "event_id" is the primary key in table A. so in order to build up the forest i need to travel in certain
    path so every parent id exists and available to me when i insert a child row.

    hope that explains better. thanks!
  • 4. Re: Connect by prior by speific order problem.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    No kidding, you need to post INSERT statements for your sample data.

    If there are 2 tables involved in this problem, then post CREATE TABLE and INSERT statements for both of them.
    If the problem involves changing one (or both) of the tables, then the INSERT statements should show the state of the tables before the changes, and the results you post will be the contents of the changed table(s) after the changes.
  • 5. Re: Connect by prior by speific order problem.
    spajdy Pro
    Currently Being Moderated
    If you need to backup data from table A to table B and then restore them back to table A try this:
    1/ add column new_id number(15) to table B
    2/ when you need copy data from table B back to table A do following:
    a/ generate new ID for rows - I suppose you have sequence for table A
         UPDATE b 
         SET new_id=<tablea_sequence_name>.NEXTVAL
         WHERE bck_backup_id=<bakcpup_id>;
       
    b/ copy data from table B to table B
         INSERT INTO A (event_id,event_name, time_stamp, parent_event )
         SELECT t.new_id,t.event_name, t.time_stamp, t1.new_id
         FROM B t
         JOIN B t1 on t.parent_event=t1.event_id
         WHERE t.bck_backup_id=<bakcpup_id>;
         

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points