Results 1 to 3 of 3

Thread: Cursor

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

    Unanswered: Cursor

    I'm trying to optimize some code in a stored procedure. I currently use a temp table to select some info from a table, evaluate it, update it, then select it from the temp table.
    INSERT INTO SESSION.RetrievedItem
    (
    ARCHIVERETRIEVALID ,
    AIMDOCUMENTID ,
    ARCHIVEDOCUMENTID ,
    CAPTUREDATE ,
    ROUTINGTRANSITNUMBER ,
    MICRACCOUNTNUMBER ,
    POSTINGACCOUNTNUMBER ,
    LOCATION ,
    SERIALNUMBER ,
    AMOUNT ,
    ITEMTYPE ,
    SEQUENCENUMBER ,
    SORRYDOCINDICATOR ,
    APPLICATIONID ,
    CREDITDEBITFLAG ,
    IMAGELENGTH
    )
    SELECT

    AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.AIMRETRIEVEDITEM.AIMDOCUMENTID, AIM.AIMRETRIEVEDITEM.ARCHIVEDOCUMENTID, AIM.AIMRETRIEVEDITEM.CAPTUREDATE,
    AIM.AIMRETRIEVEDITEM.ROUTINGTRANSITNUMBER, AIM.AIMRETRIEVEDITEM.MICRACCOUNTNUMBER, AIM.AIMRETRIEVEDITEM.POSTINGACCOUNTNUMBER,
    AIM.AIMRETRIEVEDITEM.LOCATION, AIM.AIMRETRIEVEDITEM.SERIALNUMBER, AIM.AIMRETRIEVEDITEM.AMOUNT, AIM.AIMRETRIEVEDITEM.ITEMTYPE,
    AIM.AIMRETRIEVEDITEM.SEQUENCENUMBER, AIM.AIMRETRIEVEDITEM.SORRYDOCINDICATOR, AIM.AIMRETRIEVEDITEM.APPLICATIONID,
    AIM.AIMRETRIEVEDITEM.CREDITDEBITFLAG, Length(AIM.AIMRETRIEVEDITEM.ImageFront)
    FROM
    AIM.AIMRETRIEVAL,
    AIM.STATUS,
    AIM.AIMRETRIEVEDITEM,
    AIM.ARCHIVERETRIEVAL,
    AIM.ARCHIVETYPE
    WHERE
    AIM.AIMRETRIEVAL.STATUSID = AIM.STATUS.STATUSID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
    AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'O'
    AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = decAIMRetrievalID;

    UPDATE SESSION.RetrievedItem SET AVAILABLEFLAG = 2 WHERE SESSION.RetrievedItem.ImageLength IS NULL OR SESSION.RetrievedItem.ImageLength = 0;
    UPDATE SESSION.RetrievedItem SET AVAILABLEFLAG = 1 WHERE SESSION.RetrievedItem.ImageLength IS NOT NULL OR SESSION.RetrievedItem.ImageLength > 0;

    OPEN SELECT_HIGHLEVEL;
    OPEN SELECT_CURSOR;

    Is there another way to do this that would not reuire the use of a temp table?

    Thanks in advance
    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
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    What do you think of:

    SELECT ...
    , CASE WHEN
    ImageLength IS NULL OR ImageLength = 0 THEN 2
    WHEN ...
    END AS AVAILABLEFLAG
    ,...
    FROM
    FROM
    AIM.AIMRETRIEVAL,
    AIM.STATUS,
    AIM.AIMRETRIEVEDITEM,
    AIM.ARCHIVERETRIEVAL,
    AIM.ARCHIVETYPE
    WHERE
    ...

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    That just might do the trick...thanks! I'll give it a shot...
    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
  •