Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    58

    Question Unanswered: Problem in store procedure to mirror parent & children records - Please Help!

    Hi,

    I have four tables (poaa, weakness, weakness_source and milestone)
    poaa is the parent table and can have multiple weaknesses (one-to-many relationship)
    A weakness can have multiple weakness_sources and multiple milestones.
    In other words both weakness_source and milestone tables have a
    weakness_id column to reference the pk of weakness table.

    I created a stored procedure to mirror all the parent and children
    records of a particular poaa_id record. Original records of
    that particular poaa_id is 371. I created a cursor for each of the
    the table such that the children records can be mirrored and
    new sequence number will be generated for the new records.
    I can successfully mirror poaa and weakness
    tables but new weakness_id in weakness_source and milestone
    tables are not correct. It basically updates all the records
    with the last new weakness_id even there is more than
    record in the weakness table.

    How can this be resolved? Any recommendations would be
    greatly appreciated. Thanks in advance.


    /* Original records */

    SQL> select weakness_id, poaa_id from weakness where poaa_id = 371;

    WEAKNESS_ID POAA_ID
    ----------- ----------
    689 371
    738 371

    SQL> select milestone_id, weakness_id from milestone
    where weakness_id in (select weakness_id from weakness
    where poaa_id = 371);

    MILESTONE_ID WEAKNESS_ID
    ------------ -----------
    131 689
    180 689
    182 738
    184 738

    SQL> select weakness_source_id, weakness_id from weakness_source
    where weakness_id in (689,738);

    WEAKNESS_SOURCE_ID WEAKNESS_ID
    ------------------ -----------
    286 689
    288 689
    290 738

    ================
    /* AFTER mirrored records */

    SQL> select weakness_id, poaa_id
    from weakness where poaa_id = 444;

    WEAKNESS_ID POAM_ID
    ----------- ----------
    761 444
    762 444

    SQL> select milestone_id, weakness_id from milestone
    where weakness_id in (select weakness_id from weakness
    2 where poaa_id = 444);

    MILESTONE_ID WEAKNESS_ID
    ------------ -----------
    242 762 <-- should be 761
    244 762 <-- should be 761
    246 762
    248 762

    select weakness_source_id , weakness_id from weakness_source
    where weakness_id in (761,762);

    WEAKNESS_SOURCE_ID WEAKNESS_ID
    ------------------ -----------
    342 762 <-- should be 761
    344 762 <-- should be 761
    346 762

    Here is the cursor I used for weakness_source.
    I used similar separate cursor for the milestone as well.

    CURSOR cur_weakness_source IS
    SELECT ws.weakness_source_id, ws.weakness_source_type_code,
    ws.created_date, w.weakness_id, ws.weakness_source_desc
    FROM weakness_source ws, weakness w
    WHERE w.poaa_id = pold_poaa_id
    AND w.weakness_id = ws.weakness_id;

    FOR rec_weakness_source IN cur_weakness_source
    LOOP
    SELECT weakness_source_seq.NEXTVAL
    INTO v_weakness_source_id
    FROM DUAL;

    INSERT INTO weakness_source
    (weakness_source_id
    ,weakness_source_type_code
    ,weakness_id
    ,created_date
    ,weakness_source_desc)
    VALUES
    (v_weakness_source_id
    ,rec_weakness_source.weakness_source_type_code
    ,v_weakness_id
    ,SYSDATE
    ,rec_weakness_source.weakness_source_desc);
    END LOOP;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I created a stored procedure to mirror all the parent and children records of a particular poaa_id record.
    MIRROR?
    What does a database see when it looks in a mirror?
    I have NO idea what this really means & I wonder if you know for what purpose this "mirror" is to be used.
    Why are you using HARDCODED values in your code?
    What happens when different data in entered?

    >242 762 <-- should be 761
    If you say so.
    I don't see how most of your data/examples relate to each other.

    >How can this be resolved?
    I do not know what "this" is.
    What problem are you really trying to solve?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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