Hi Sanjay, maybe you could store the short name of the folder name as an additional column, lets say fldrFolderName, of PMP_OWNER.JOBS. If you don't like the redundancy, you may split fldrName into columns fldrPath and fldrFolderName and construct the complete folder name as concatenation of fldrPath || '\' || fldrFolderName ; And if you create an index on fldrBaseName sorting should be very fast.
Best Regards, Thomas >-----Urspr�ngliche Nachricht----- >Von: sanjay soni [mailto:[EMAIL PROTECTED] >Gesendet: Dienstag, 26. Oktober 2004 08:47 >An: [EMAIL PROTECTED] >Betreff: 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]
