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

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
...

That just might do the trick...thanks! I'll give it a shot...