Inserting Parent Keys into a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Inserting Parent Keys into a table

  1. #1
    Join Date
    Nov 2003
    Posts
    31

    Inserting Parent Keys into a table

    Hi All,

    I am trying to do an outer join on two tables and insert the resulting values into a
    third table. In addition, I also need to insert all the parent keys to the row that I
    am inserting into this table. I have provided the details below.


    create table target (PART_ID VARCHAR2(256), Q_ID NUMBER(38));
    create table qtab (Q_ID NUMBER(38), SERIAL_NO VARCHAR2(20));
    create table sers (PART_ID VARCHAR2(256), SERIAL_NO VARCHAR2(20));
    create table pq_tab (Q_ID NUMBER(38), PARENT_Q_ID NUMBER(38));

    ALTER TABLE pq_tab ADD ( PRIMARY KEY (q_id) );
    ALTER TABLE qtab ADD ( PRIMARY KEY (q_id,serial_no) );
    ALTER TABLE sers ADD ( PRIMARY KEY (part_id, serial_no) ) ;

    insert into qtab values (64,'A1-b');
    insert into sers values ('1A', 'A1-b');
    insert into pq_tab values (64,63);
    insert into pq_tab values (63,48);
    insert into pq_tab values (48,0);


    SQL> select a.part_id, b.q_id from sers a, qtab b where a.serial_no=b.serial_no;

    64 1A

    Now the part_id, q_id selected above should be inserted into the target table's part_id
    and q_id respectively. In addition to this, I will need to enter all the parent_q_ids
    into this table as well for a given part_id. So, for the given data, the target table
    should have the following data:

    select * from target:

    part_id q_id
    ======= ====

    1A 64
    1A 63
    1A 48

    I am not quite sure how to accomplish this. Here is what I need help with.

    1) Insert the above 2 values into the target table.
    2) Also insert all the parent_q_ids for 64 (63 and 48) into the target table.

    I have been trying to use the CONNECT BY statement but was unable to come up with the appropriate command.

    I am running Oracle 9.2.0.2.0 on Solaris 9.

    Any help would be greatly appreciated.

    Please let me know if you need further details.

    Thanks,
    Sankar.
    Last edited by sankar6254; 06-13-2005 at 09:40 PM.
    Sankar B. Mandalika

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    For a given part_id, the below sql gives the required resultset.
    PHP Code:
    var b1 varchar2(30);
    exec :b1 := '1A';

    select :b1 part_idq_id
      from pq_tab
      start with parent_q_id 
    =  (select parent_q_id
                                  from pq_tab
                                 where q_id 
    =
                                    (
    select b.q_id
                                       from sers a
    qtab b
                                      where a
    .serial_no b.serial_no
                                        
    and a.part_id = :b1)
                                 )
      
    connect by prior parent_q_id  q_id
    /

    PART_ID                                Q_ID
    -------------------------------- ----------
    1A                                       64
    1A                                       63
    1A                                       48 
    Test the SQL statement with many rows in all tables. This is very important. With limited data, the SQL may return correct result set.

    You have to ensure that sub queries return a single row. Other wise it will blow out. May be you need to use max or min in the sub queries.
    Tamil
    Last edited by tamilselvan; 06-14-2005 at 03:02 PM.

  3. #3
    Join Date
    Nov 2003
    Posts
    31
    Hi Tamil:

    That was perfect. Worked like a charm. Thanks for your quick help.

    Best Regards,
    Sankar.
    Sankar B. Mandalika

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I never designed a DB in which an entity has self references. Some how I dislike it.

    Tamil

Posting Permissions

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



Click Here to Expand Forum to Full Width