Subject Re: INVALID INDEX DESCRIPTOR From Mervyn Bick <invalid@invalid.invalid> Date Thu, 17 Jul 2014 10:05:07 +0200 Newsgroups dbase.getting-started On Thu, 17 Jul 2014 03:41:19 +0200, Bruce Heath <bah@specsol.com.au> wrote: > Hi - I'm new to Visual dBase and have just purchased Dbase 9. > I'm trying to create a table with an attached index. > > Table creates - no problems - only 3 columns and about 3000 rows. > > Go to create index ("complex" type) with structure : > > TRIM(field_1)+TRIM(field_2)      **Both fields are character type > > On trying to save the file I get : > > "Database Engine Error :  Invalid Index Descriptor : D:\DBASE9\IC   > PROGRAMS\Tem5BBC.tmX" > > I assume that the file involved is a temporary file being renamed by the   > DBE to something else and stalling - but I don't know why! > > Any ideas? > > Regards, There are two types of Index in dBASE.  One is a dBASE index on tables   held in .DBF tables and this type of index can be built on a complex   expression involving more than one field.  If different field types are   used in the index expression the values in the fields must all be   converted to character type.  When including dates one should user   dtos(fieldname), which produces a value in the form yyyymmdd, to ensure a   correct chronological order.  NEVER use trim() or ltrim() when building an   index expression.  As Ivar has pointed out, there is a very clear warning   on this in the online help. The basic syntax (see the online help for the full syntax) for building   this type of index is INDEX ON <key exp> TAG <tag name> With this type of index dBASE has to be told specifically to use the index   either in the USE statement when using the old XDML commands or by setting   a query's indexName property when using OODML. The other type of index is a SQL index.  It is more applicable to tables   held on a SQL server but it can be created for .dbf tables as well.  It is   not normally required to create such indexes as SQL will create temporary   indexes automatically.  The existence if an existing SQL index can,   however, speed up data retrieval particularly with large tables. SQL indexes are not applied by name.  Instead an ORDER BY clause is used   in the SQL statement.  If an appropriate index exists SQL, or localSQL   with .dbf tables, will use it without having to be told to. Creating the primary index for a SQL table is done when the table is   created.  The syntax for creating a secondary SQL index is CREATE INDEX <index name> ON <table name> (<column name> [, <column   name>...]) Note. The brackets round the fieldnames are required where more than one   field is specified. If you have used CREATE INDEX to create an index then the expected index   expression would be (fields_1,field_2)   The field_1+field_2  (with or   without the trim() functions) is invalid as SQL doesn't understand the +   in this context hence the error. An ORDER BY clause can mix ascending and and descending field values eg   ... Order by field_1 asc, field_2 desc In the case of an index the direction of the individual fields in an index   cannot be qualified but the index itself can be either ascending or   descending. dBASE Plus 9 comes with a copy of the second edition of Ken Mayer's "The   DBASE Book" and this should go a long way to getting you started with   dBASE. Mervyn.