Error building the key for index ... (Error 2199) 1

[Olaf Doschke]

The index expression causing the error as given in this thread title is

IIF(logicalfield,"0","1")+TRANSFORM(integerfield)

The concrete values of the record to be indexed are .T. and 48246, resulting in the index key value "048246". I feared I had a .NULL. creeping in, but that's not the case.

From the help topic about error 2199 the error happens, if an index expression may exceed the maximum length of 128 bytes, eg if you would index on a memo field. But then - AFAIR - the index creation itself would already error, which it doesn't. You can INDEX ON IIF(logicalfield,"0","1")+TRANSFORM(integerfield) TAG order_ix ASCENDING ADDITIVE.

I see TRANSFORM() does not pad to a static length, and the result value of a TRANSFORM may get as long as you like, if you'd pass in a string, for example. Indeed this index used for sorting data only works, as the value range of the integers is between 10000 and 99999, always the same length.

The error happens when executing in VFP9 and not in VFP7, so I'd say VFP9 has become stricter about what index expressions it allows. The error happens with the first record inserted. I can fix it using STR() instead of TRANSFORM(), though I have memories about indexes on STR() having a bug in VFP7.

Now the final though and tip about this: If you ever come across error 2199 check, whether your index expression may not strictly always have the same width. It doesn't matter if the width varies below 128 bytes, varying lengths alone are reason for VFP9 to error, as it seems.

And last not least: The rule of thumb is to pad an index expression, if needed, so it always has the same length below 128 bytes.

Bye, Olaf.

 

[Tore Bleken]

Correct, VFP9 has a much stricter error checking. So many errors are stopped before they "bite" you. In this case a much better index expression would be

IIF(logicalfield,"0","1")+padl(integerfield,5,'0')

 

[Dan Freeman]

Yes, this has been a "gotcha" in the language at least as far back as FoxBase+ (and probably all the way back to dBase II).

I've seen systems that were intermittently flaky suddenly become rock solid just from fixing index expressions.

 

[Olaf Doschke]

I wouldn't expect to be bitten only when the first index key is generated from the first record inserted.

Actually you can do this:

On Error ? 'Line:',Lineno(), Message()

Create Cursor curTest (llogical L default .F., iint i, mMemo M)
Index on Iif(llogical,"0","1")+Transform(iint) TAG sortindex
Insert Into curTest Values (.T.,0,'')
Insert Into curTest Values (.F.,1,'')
Insert Into curTest Values (.T.,10,'')
Index on mMemo Tag fails

Just the last two lines fail, the third insert and the index on a memo field. So VFP9 seems to accept an index, even if not having a fixed length, just refusing the first key value longer than the first indexed value.

But in my case the error occurred for the first and only inserted record, which is unusual, and indeed the PadL() expression works as well as using STR() and uses less bytes.

Anyway, you got to be cautious with this.

Bye, Olaf.

 

[Dan Freeman]

This just goes hand in hand with the age-old maxim "NEVER USE TRIM() IN INDEX EXPRESSIONS!"

In some cases VFP will actually pad whatever you've trimmed to a consistent length, but I believe it's arbitrary and subject to the same behavior you're seeing here.

 

[vgulielmus]

Very interesting. TRANSFORM(Numeric) can't be used to build an index
But TRANSFORM(character) raised no errors.
All these fails:

CLEAR
CLOSE DATABASES ALL 
On Error ? 'Line:',Lineno(), Message()

Create Cursor curTest (iint I) && N(2) && F(2) && B(2) && Y
Index on Transform(iint) TAG sortindex
Insert Into curTest Values (11)

A DEFAULT clause make no difference :

CLEAR
CLOSE DATABASES ALL 
On Error ? 'Line:',Lineno(), Message()

Create Cursor curTest (iint I DEFAULT 99) && N(2) && F(2) && B(2) && Y
Index on Transform(iint) TAG sortindex
Insert Into curTest Values (11)

If the index is be build after at least one value big enough is added, then insert succeded:

CLEAR
CLOSE DATABASES ALL 
On Error ? 'Line:',Lineno(), Message()

Create Cursor curTest (iint I DEFAULT 999)
APPEND BLANK
Index on Transform(iint) TAG sortindex
Insert Into curTest Values (99)

Clearly if the cursor is empty, index on transform(numeric) is similar to index on transform(0)

But characters datatype behaves nicely. All these succeded:

CLEAR
CLOSE DATABASES ALL 
On Error ? 'Line:',Lineno(), Message()

Create Cursor curTest (iint C(3)) && V(3)
Index on Transform(iint) TAG sortindex
Insert Into curTest Values ("11")

Obviously VFP force the length of the index to the length of the field.
Now I'm asking why doesn't take into account the length of the numeric field, too ? At least Numeric is stored as a string in the dbf.

One final test :

CLEAR
CLOSE DATABASES ALL 
lclc = "1"
On Error ? 'Line:',Lineno(), Message()

Create Cursor curTest (iint C(3)) && V(3)
Index on Transform(m.lclc) TAG sortindex
Insert Into curTest Values ("11") && succed
lclc = "22" 
Insert Into curTest Values ("11") && fail

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

It looks like the mechanism to determine the result cursor schema, if you query with expressions. The first record determines the size of char fields, for example. And with indexes the first key determines the index key size. If further keys are longer, they trigger an error.

In my case the odd thing still is the first key triggered an error, instead it could define the index key size and no further records would break that in this specific case, as all ints are in the 5 digits long value range. This code is inherited, but this time from my ex boss or colleague. They should know better. I did use STR() way back in VFP6/7, as it has the fixed width, before I knew BINTOC() to be best choice in case of compound index expressions with int fields.

Bye, Olaf.