Hello Sanjay, The expression
SUBSTR(FOLDERNAME, INDEX(FOLDERNAME, '\', 1, LENGTH(FOLDERNAME) - LENGTH(REPLACE(FOLDERNAME, '\'))) + 1) does the trick of extracting the last folder name, as you require. So you can possibly avoid the stored procedures and the explicit copying at all. Regards Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: sanjay soni [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 26, 2004 8:47 AM > To: [EMAIL PROTECTED] > Subject: Is there any other way of doing this????? > > Hi, > 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 inbuilt support > from sapdb for doing all this. > > 3. Copy the data got from base table + modified foldername in to temp > table. This temp table is created at runtime only in the > stored procedure itself. > > 4. Sort the temp table on foldername. > 5. Drop the temp table. > 6. 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. > > All the above I discussed is working fine. > Now problem is that temp table creation and copying data to > temp table is an expensive operation. So if there are 10,000 > records to be copied to the temp table at runtime, it will > affect the performance of the system. > > Is there any other way of doing all this????? > > > > > Here is the code for stored procedure: > > 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; > > /*Drop Table, as we dont need it*/ > DROP Table TEMP.TempJOBS; > > $CURSOR = 'ReturnCursor2'; > DECLARE $CURSOR CURSOR FOR > Select * from PMP_OWNER.ReturnCursor; > END; > > > > > > > > > > > --------------------------------- > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We finish. > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
