Results 1 to 2 of 2

Thread: Loop

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Loop

    I think I may have posted about this before but not sure:

    I have a procedure with a LOOP statement. It mostly works; it gets the first record it finds that matches the criteris, does the relevant update, and then that's it: it only updates one record although there are 95 records that match the search criteria.

    Any advice on cleaning tyhis up would be appreciated.

    Thanks in advance!

    CREATE PROCEDURE AIM.UpdateArchiveRetrievalStatus (OUT COUNTER INTEGER )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- April 13, 2004
    -- Anthony Robinson
    -- Updates Status of ArchiveRetrieval. If EstimatedResponseTime has passed,
    -- retreival status is set to CompleteWithErrors
    ------------------------------------------------------------------------
    SPECIFIC UPDATEARCRETSTS
    LANGUAGE SQL
    RESULT SETS 0
    P1: BEGIN

    DECLARE intImageCount INTEGER;
    DECLARE decArchiveRetrievalID DECIMAL (13,0);
    DECLARE AT_END int DEFAULT 0;
    DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
    DECLARE UPDATE_CURSOR CURSOR FOR
    SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP
    AND STATUSID = 2;

    DECLARE CONTINUE HANDLER for NOT_FOUND
    SET AT_END = 1;

    OPEN UPDATE_CURSOR;
    FETCH_LOOP:
    LOOP
    FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
    SET intImageCount = (SELECT COUNT(IMAGEFRONT) FROM AIM.AIMRETRIEVEDITEM WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID
    AND IMAGEFRONT IS NOT NULL);
    IF AT_END = 1 THEN
    LEAVE FETCH_LOOP;
    ELSEIF intImageCount = (SELECT ITEMCOUNT FROM AIM.ARCHIVERETRIEVAL WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID) THEN
    ITERATE FETCH_LOOP;
    END IF;
    UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;
    SET AT_END = AT_END + 1;
    END LOOP FETCH_LOOP;
    --SET COUNTER = AT_END;
    CLOSE UPDATE_CURSOR;
    --UPDATE AIM.ArchiveRetrieval
    --SET StatusID = 3 WHERE

    END P1
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Choices:

    a. use IF (sqlcode = 100) OR (sqlcode < 0) THEN
    LEAVE fetch_loop;
    END IF;
    your problem appears that you are incrementing at_end after the successful process of a row. So it is hitting your if at_end = 1 then leave statement even though it fetched a 2nd successful row.

    b. Or change LOOP to WHILE

    OPEN cursor
    FETCH
    WHILE SQLCODE <> 0 DO
    some code
    END WHILE;

Posting Permissions

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