Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Issue: index zero lenght control and documentation
Message
From
28/12/2004 13:24:41
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Other
Title:
Issue: index zero lenght control and documentation
Environment versions
Visual FoxPro:
VFP 9
Miscellaneous
Thread ID:
00972651
Message ID:
00972651
Views:
50
I Read on documentation:
When Visual FoxPro creates an index, it evaluates fields in the first record in the table.
Therefore, if the index expression is a substring of an empty memo field, 
a zero-length index key is created. 

Tip:  
To prevent creating a zero-length index key if a field is empty, 
it might be necessary to enter some temporary data in the field of the first record.
This is true for temporary index that SQL engine build, but it is not true
for the INDEX direct manipulation;
on this case VFP check the first and the last record!
( then it is very hard to control the index key )
clear
ON ERROR ? MESSAGE()
CREATE CURSOR testindex ( f1 char(1),l1 I)
INSERT INTO testindex VALUES ('',1)
INDEX ON left(f1,l1) TAG t1

INSERT INTO testindex VALUES ('',0)
? "reindex"
REINDEX

INSERT INTO testindex VALUES ('',1)
? "reindex"
REINDEX
An side effect is that when VFP build a temporary index,
the length of the key can to be different,
and therefore it can return different results.

Another thing, i look "substring" into the doc paragraph, well:
"you cannot index a SUBSTR(memoField) because SUBSTR return a Memo type"
CREATE CURSOR testindex ( f1 M)
INSERT INTO testindex VALUES ('1')
INDEX ON SUBSTR(f1,5,10) TAG t1
Workaround:
To fill up VFP with CAST(AS C()).

Fabio
Reply
Map
View

Click here to load this message in the networking platform