help please with 'Alter Table'

[ron9999]

Hi All,
I like to change the expression of the index key
the table has 9 indexs

I check the first index like:
IF !(TAG(1)== 'BELEGNAME' AND KEY(1) == 'BELEGNAME')
INDEX ON ALLTRIM(UPPER(BELEGNAME)) TAG BELEGNAME
ENDIF

how to I change the key from:
'BELEGNAME' to ALLTRIM(UPPER(BELEGNAME))

TIA
Ron

 

[Olaf Doschke]

The code you posted would add an index with tag name BELEGNAME and index expression (=KEY) ALLTRIM(UPPER(BELEGNAME)), but only if the first Tag and Key are not BELEGNAME.

But that would only add such an index, it wouldn't replace an old one named BELEGNAME and it could give an error and not succeed if some other of the 9 tags is already named BELEGNAME. Tag names must be unique.

So you should simply DELETE the old index to create the new one:

SELECT Table
DELETE TAG Belegname
INDEX ON ALLTRIM(UPPER(Belegname)) TAG Belegname

But: DELETE TAG will (also) throw an error, if no index of that tag name exists. So you'd perhaps need to check that within a loop over all tag names first, or simply catch that error with ON ERROR llError=.T. before and ON ERROR afterwards:

But2: Foxpro will always make an index a constant width, so ALLTRIM() is useless. It doesn't harm, but the CDX file will not be shorter through this. I'd recommend using only UPPER(Belegname) as index expression.

So I'd do:

LOCAL llError
ON ERROR llError = .T.
SELECT Table
DELETE TAG Belegname [OF Tablename.cdx]
ON ERROR
INDEX ON UPPER(Belegname) TAG Belegname

Bye, Olaf.