Hi all

I need some ideas on how to do the following:

The record being read in has the following 3 part key

  156884PT9*10088488*91

I am building a new table with this information but I need to move the original first key to the end so when the new key will be:

  10088488*91*156884PT9

I need a routine that will switch the keys - any ideas on how to do it? It seems like it should be simple, but I believe I'm not seeing the forest for the trees.

Thanks

Andy Becker

Illinois State Lottery

Solved my own problem.

Found a handy-dandy little command called FIELD. Works like a champ.

I was originally trying to manually do what this command does. Glad I was looking in the manual for something else when I tripped over this.

Thanks

Assuming that these keys are not buried in any other related tables, the process would be:

1. Make a new temporary table to hold the new records / keys. No index and no dictionary. Just a data table.

2. Select the original table:

 OPEN 'OLDTABLE' TO FILE_IN ...

 OPEN 'NEWTABLE' TO FILE_OUT ...

 SELECT FILE_IN   .. do an R/BASIC SELECT .. QUICKER

3. Copy the existing records with new keys:

L0:

READNEXT @ID THEN

  READ OLDREC FROM FILE_IN,@ID THEN

     P1=FIELD(@ID,'*',1)

     P2=FIELD(@ID,'*',2)

     P3=FIELD(@ID,'*',3)

     NEWKEY=P3:"*':P2:'*':P1

     WRITE OLDREC ON FILE_OUT,NEWKEY ELSE

       ... handle the write error

     END

  END ELSE

    ... handle the read error

  END

  GOTO L0

END ELSE

 ... end of job logic

END

If you make a backup of your old table then you can delete the original source records as they are processed. However, if the original table is indexed, then this may make the process too lengthy

When the records have been copied to the new table you can do either of the following:

1.  CLEARTABLE OLDTABLE. Copy the records from the new table into the old table

2.  Do a DOS RENAME:

    .. find the .LK and .OV names for your old table and new table

    RENAME REVxxxxx.LK REVxxxxx.LK1   ;* old table

    RENAME REVxxxxx.OV REVxxxxx.OV1

    RENAME REVyyyyy.LK REVxxxxx.LK    ;* new temp table

    RENAME REVyyyyy.OV REVxxxxx.OV

    .. then rebuild the indexes

Either way will work

HTH

Don

You were faster at looking things up than I was at writing code. Glad you've solved your problem.

Don

View this thread on the forum...