Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Any Hints On UPDATE Cursor

    I have written a stored procedure that uses a cursor to select values from a table. It chooses them one at a time, evaluates the value, and then depending on whether or not criteria is met, a corresponding row is updated in a table.

    When running the procedure, I'm not getting the expected results.

    Could someone maybe give me a clue as to what I may be missing in my stored procedure?

    Thanks in advance...

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

    DECLARE intImageCount INTEGER;
    DECLARE decArchiveRetrievalID DECIMAL (13,0);
    DECLARE UPDATE_CURSOR CURSOR WITH HOLD FOR
    SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP
    AND STATUSID = 2;-- FOR UPDATE ;--OF AIM.ARCHIVERETRIEVAL;

    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 intImageCount = (SELECT ITEMCOUNT FROM AIM.ARCHIVERETRIEVAL WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID) THEN
    LEAVE FETCH_LOOP;
    UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;
    END IF;
    END LOOP;
    --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 2001
    Location
    UK
    Posts
    4,650
    UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;

    can be
    UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE current of UPDATE_CURSOR

    I think you can achive this proc's functionality using a SQL Statement ..

    Have you tried ?

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    I'd love to use a straight SQL statement, but my limited ability to "think out of the box" has me kinda dumbfounded.

    The cursor is ther first thing I though of to itierate through the multiple records, evaluate, then update...

    I tried something like a SQL statement but endedup doing a blanket update on every row that had EESTIAMTEDREPSONSETIME < CURRENT TIMSTAMP.

    Any suggestions?

    Thanks for the reply!!
    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..."

Posting Permissions

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