Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    6

    Unanswered: Cursors..the bane of my life at the moment

    Hi all,

    I think the best way to describe my issue is though an example , then I will show my code for my solution so far and hopefully it will help you see what I'm aiming towards.

    I have this table below and each row maps to an attribute I have on a webpage form

    --ID--Package_ID--Sequence_ID--Group_ID
    --1-|||-----30----|||----1-----|||---2
    --2-|||-----30----|||----1-----|||---2
    --3-|||-----5-----|||----3-----|||---4
    --4-|||-----5 ----|||----3-----|||---4

    and I have performed a query as seen above to pull back all the attributes which are sharing same sequence ID's. Now as you can see the first 2 rows/attributes belong to the same group so they will be displayed on the page in the same section. The order in which they are displayed is determined by the sequence ID, however these are the same which means if a package with ID of 30 is chosen then when it comes to displaying these attributes they will override each other as both will try to be displayed in position 1, thus only one will be seen(not what I want).

    The same problem can be seen for the last 2.

    I need a way to go through each of these 'duplicates' that I have been able to discover, and are demonstrated in the table above, and then update the sequence ID to the next number. Then in turn incrementing all the sequence_ID's of the other attributes with the same group and package ID so it can't overwrite them either. This is my code so far, but I'm stuck at this point.


    DECLARE
    CURSOR duplicateDisplaySeq
    IS
    select ID,sequence_id , Package_ID, Group_ID
    from attribute_lkp sgal2
    where (ID,sequence_id, Package_ID)
    in (select distinct sgal1.ID,sgal1.sequence_id, sgal1.Package_ID
    from attribute_lkp sgal1
    where (select count (distinct attribute_name)
    from attribute_lkp sgal2
    where sgal1.ID = sgal2.ID
    and sgal1.sequence_id = sgal2.sequence_id
    and sgal1.Package_ID = sgal2.Package_ID) > 1)
    order by ID, Package_ID;

    v_newdisplaysequencenumber NUMBER;

    BEGIN

    FOR REC IN duplicateDisplaySeq
    LOOP

    v_newdisplaysequencenumber := REC.sequence_id + 2;

    update attribute_lkp
    set sequence_id = 2
    where Group_ID = REC.Group_ID;

    FOR duplicateRow IN (select sequence_id, Group_ID
    from attribute_lkp
    where ID = REC.ID
    and Package_ID = REC.Package_ID
    and sequence_id >= REC.sequence_id
    order by to_number(sequence_id))
    LOOP

    EXIT WHEN (duplicateRow.sequence_id >= v_newdisplaysequencenumber);

    update attribute_lkp
    set sequence_id = (v_newdisplaysequencenumber)
    where Group_ID = duplicateRow.Group_ID;

    v_newdisplaysequencenumber := v_newdisplaysequencenumber + 1;

    END LOOP;

    END LOOP;
    END;


    So if I was doing this for ID 1 it would need to be incremented 2, then run a separate query to pull all the other attributes that are in that group (group 2) and relate to that package ( package 30) and increment they're sequence id.

    That on it's own isn't the issue, the issue presents itself when I come to apply these actions on the next record in the cursor, it will no longer be relevant as I've updated one of the offending sequence_ID pair to a new Sequence_ID (of 2) so the fact it's sequence ID is 1 is now valid. So I need a way so it re evaluates the cursor every time it goes back to the FOR that is using it, so that when it comes to get the next record the cursor is up to date and only has valid duplicates.

    Or if I use the For with the select statement like I have in one part of the code, does it run the query every time it loops back to the FOR so it will have a fresh result set? I hope I've explained this clearly as it really is getting me stumped. If a for with a select query does run the query again each time it's hit and works from the new results I think I've it sorted.

    Thanks in advance for the help everyone
    Last edited by greyfloppyhat; 08-20-12 at 16:01.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since we don't have your tables or data, we can not run, test, improve or debug posted code.

    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    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.

  3. #3
    Join Date
    Aug 2012
    Posts
    6
    I don't have access to them at the moment, but I will pull the details down tomorrow morning.

    I'm more looking into the theory, I'm wondering does a for loop statement that explicitly states the select query re run that query every time it loops back to the beginning of said for loop

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I'm wondering does a for loop statement that explicitly states the select query re run that query every time it loops back to the beginning of said for loop
    NO.
    SQL SELECT is parsed once.
    Each time through the LOOP another row gets returned until NOMOREROWS get signaled.
    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.

  5. #5
    Join Date
    Aug 2012
    Posts
    6
    Is there any iterative structures that would allow a select to be re run every iteration? Or could I grab a result set every iteration?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    write yet another outer LOOP
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by greyfloppyhat View Post
    Hi all,

    I think the best way to describe my issue is though an example ,

    <4001 characters deleted>

    Thanks in advance for the help everyone
    So, in other words, you would like to renumber sequence_ids to be unique within each group_id, and update the table accordingly. Correct?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Aug 2012
    Posts
    6
    Quote Originally Posted by n_i View Post
    So, in other words, you would like to renumber sequence_ids to be unique within each group_id, and update the table accordingly. Correct?
    Yes, that's pretty much what I'm looking to do.

    I'm not sure how putting another outer loop could help, am I missing something really simple?

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by n_i View Post
    So, in other words, you would like to renumber sequence_ids to be unique within each group_id, and update the table accordingly. Correct?
    You don't need a loop for that.

    A simple MERGE statement will be enough.

    Something like:
    Code:
    merge into attribute_lkp
    using (
       select row_number() over (partition by group_id order by id) as new_sequence,
              id
       from attribute_lkp
    ) t on (t.id = attribute_lkp.id)
    when matched then update
       set sequence_id = t.new_sequence;
    (This assumes that id is the primary key)

    Doing SELECTs or UPDATEs in a loop is always a warning sign and should be avoided as much as possible. Relational databases are not designed to do stuff row-by-row.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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