create-index Command

The create-index BASIC program creates a new B-tree index in the specified file using the a (algebraic) processing code expression provided and ensures the a0 index case-sensitivity matches the item-ID case sensitivity.

NOTE	D3 does not support indexes on sub-values. For the a0 index only, the c option must be used if the file was created with the s option. If the file was not created with the s option, the c option may not be used when creating the a0 index for that file.

Syntax

create-index file.reference a.code {(options)}

create-index file.reference attribute.defining.item {(options)}

create-index file.reference * {(options)}

Parameter(s)

a.code

Specifies the a processing code to be used in forming the keys to the index. The processing code must include an attribute number immediately after the a and cannot contain any named attribute references. The first attribute number in the processing code is the master attribute. This attribute determines the number of values that an index key generates.

NOTE: The processing code does not require an attribute number immediately after the a on D3 Windows FSI. However, these indexes can only be used in BASIC. They will not be used by the update processor or Access.

Regarding the use of translates when creating indexes

For Windows: Great caution should be taken when using translates during index creation since index corruption can occur. To employ translates,the file reference must contain the full path to the file. For example:

create-index file1 a1(tfsidm,tfile2,;x;;1))

For UNIX: Use the s (Subindex) Processing Code to employ translates.

specifier

Designates all indexes. This may be used as an alternate to a series of create-index commands by placing the i processing codes directly into the correlative position of file-defining item (D-pointer), and then using the asterisk with the create-index command.

options

Specifies the keys are case sensitive. Default is case insensitive. If the * specifier is used, the index(es) will remain case insensitive.

As of D3 9.0, you can create an index with a casing different than what is established by the TCL case command (defaults to off), but AQL will not use it unless the TCL case setting matches the index casing. The BASIC root and key statements will use the specified index that matches the TCL case command setting, unless the BASIC casing statement is used to override it.

For Windows FSI only: Creates floating-point indexes (where the keys are floating-point numbers, such as '1.234E123'. 2). Index keys may be normalized (plus signs and non-significant digits are removed, and decimal points are moved). For example, '+00123e12' is entered in the index as '123e12'.

TIP—If the keys do not have exponents (for example, '123.456', use numeric indexes instead.

Locks the file on which it is currently working to facilitate parallel access by other users. The l option forces a create-index to lock the entire file during index creation.

Creates numeric indexes. Numeric indexes are sorted according to their numerical value, (for example, "10.5" is greater than "9.34" and "9 June 1980" is less than "1 Aug 2010").

When searching for the first entry in the index, use the smallest expected negative number as the first search key. The empty string "" is considered to be 0. For example:

s=-9999

i=''

key('n',r,s,i) then...

finds -9000, but does not find -10000.

Inserting non-numeric keys in the index produces unpredictable results.
Because the locale is used to determine the thousands and decimal separators, it is strongly recommended to store numerics in scaled internal format (that is, with no decimal or thousands separators {-} n {n...}). This insures correct behavior when accessing the index from locations with different locales.

NOTE—For numeric indexes to work properly in the vme, numeric data must be stored in scaled internal format (that is, with no decimal or thousands separators {-} n {n...}). This ensures correct behavior when accessing numeric indexes.

Recreates the index, overriding the previous version.

Suppresses the display of the running count of items.

WARNING

It is not valid to create an index with a translate to the same file.
Creating an index with a MultiValued a processing code (whether created with an a code or an attribute-defining-item), is not supported.

An a processing code, attribute defining item or an asterisk must be specified.

Once an index has been created, any changes made to the file through any process that updates the file are automatically reflected in the index.

An existing index can be overwritten using the o option.

The pointer to the index is placed in the correlative attribute of the file-defining item (attribute 8) as an i processing code.

VME indexes

The number following i indicates the root FID of the index in the VME. This should never be manually altered.

VME index keys have a maximum length of 249.

FSI indexes

The number following i indicates the number of the index in the FSI. This should never be manually altered.

FSI index keys have no maximum length.

Example(s)

Creates an index on the contents of attribute 1.

create-index entity a1

Creates an index on the results returned from attribute 3 of invoices, translated to customer.file, and returning attribute 1 from the item, if found.

create-index invoices a3(taccount,customer.file,;x;;1)

Creates an index on attribute 1 concatenated with attribute 3.

create-index sales a1:3

The example below illustrates how a series of indexes can be built.

Index references are placed in the correlative attribute (8) of the (entity) files D-pointer. For example:

correlative Ia1

Ia23

The command is issued.

create-index entity *

Afterwards, the correlative attribute contains the root FID for each of the indexes.