I have written one stored procedure.
What I am trying to do is:
1. get the data from base table.
2. modify the data of one column(FOLDERNAME)
Well here, value of foldername looks like
"c:\folder\Myfolder". But I want only Myfolder from this data. So we
have written another stored procedure which extract the data and
store it in output parameter, because there is no inbuild support
from sapdb for doing all this.
3. Copy the data got from base table + modified foldername in to temp
table.
4. Sort the temp table on foldername
5. Return it as a cursor
It is must that I dont touch the original data, but also data must be
sorted on the modified foldername and returned it as cursor.
When I compile this procedure, it compiles properly, and when I run
it, I get the error message,
---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
General error;-4024 .
call UPDATERESULTSET_CURSOR3
I guess something is wrong with temp table, but dont know what??
Here is the stored procedure
CREATE DBPROC UPDATERESULTSET_CURSOR3 returns CURSOR AS
VAR
JINDEX INT;
JID VARCHAR(64);
CUSERID INT;
AUSERID INT;
FLEINDEX INT;
FLDRNAME VARCHAR(255);
FLENAME VARCHAR(1024);
JNAME VARCHAR(255);
TMPFLDRNAME VARCHAR(1024);
WRKFLOWFILENAME VARCHAR(1024);
ARCHIEVEFLDRNAME VARCHAR(1024);
TRGTNAME VARCHAR(255);
USRNAME VARCHAR(255);
JNO INT;
CHANGEDFLDRNAME VARCHAR(255);
BEGIN
CREATE TABLE TEMP.TEMPJOBS(TMPJOBINDEX INT,
TMPJOBID VARCHAR
(64),
TMPCREATEDUSERID
INT,
TMPASSIGNEDUSERID
INT,
TMPFILEINDEX INT,
TMPFLDRNAME
VARCHAR(255),
TMPFILENAME
VARCHAR(1024),
TMPJOBNAME VARCHAR
(255),
TMPTEMPFLDRNAME
VARCHAR(1024),
TMPWRKFLOWFILENAME VARCHAR(1024),
TMPARCHIEVEFLDRNAME VARCHAR(1024),
TMPTARGETNAME
VARCHAR(255),
TMPUSERNAME
VARCHAR(255),
TMPJOBNO INT);
DECLARE MYCURSOR CURSOR FOR
SELECT * FROM PMP_OWNER.JOBS;
WHILE $RC = 0 DO BEGIN
FETCH NEXT MYCURSOR
INTO :jIndex, :jID, :cUserID, :aUserID, :fleIndex, :fldrName,
:fleName, :jName, :tmpFldrName, :WrkFlowFileName, :ArchieveFld
rName,
:TrgtName, :UsrName, :jNo;
IF($rc = 100) THEN break;
CALL GETFOLDERNAME2(:fldrName, :changedFldrName);
INSERT INTO TEMP.TEMPJOBS(TMPJOBINDEX ,
TMPJOBID,
TMPCREATEDUSERID,
TMPASSIGNEDUSERID,
TMPFILEINDEX,
TMPFLDRNAME,
TMPFILENAME,
TMPJOBNAME,
TMPTEMPFLDRNAME,
TMPWRKFLOWFILENAME,
TMPARCHIEVEFLDRNAME,
TMPTARGETNAME,
TMPUSERNAME,
TMPJOBNO)
VALUES(:jIndex,
:jID,
:cUserID,
:aUserID,
:fleIndex,
:changedFldrName,
:fleName,
:jName,
:changedFldrName,
:WrkFlowFileName,
:ArchieveFldrName,
:TrgtName,
:UsrName,
:jNo);
END;
DECLARE ReturnCursor CURSOR FOR
Select * from TEMP.TEMPJOBS order by TMPFLDRNAME;
DECLARE ReturnCursor2 CURSOR FOR
Select * from PMP_OWNER.ReturnCursor;
END;
---------------------------------
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!
