Click above to get retro games delivered to your door ever month!

 SUBINDEX
 Create a subindex based on another index
------------------------------------------------------------------------------

 Syntax 1    Syntax 1 will create a new .ntx or .idx index, depending on
                 the RDD used to open the table in the current work area.

     SUBINDEX ON <expKey> TO <cIndex>
        [FOR <lCondition>]
        [WHILE <lCondition>]
        [ASCENDING | DESCENDING]
        [UNIQUE]
        [NONCOMPACT]
        [ADDITIVE]
        [<scope>]
        [EVAL <lCondition> [EVERY <nRecs>]]


 Syntax 2    Syntax 2 creates a compound (.cdx) index or a new tag in
                 a compound index.

     SUBINDEX ON <expKey> TAG <cTagName> [TO <cCDXName>]
        [FOR <lCondition>]
        [WHILE <lCondition>]
        [ASCENDING | DESCENDING]
        [UNIQUE]
        [ADDITIVE]
        [<scope>]
        [EVAL <lCondition> [EVERY <nRecs>]]

     <expKey> is an expression that returns the key value to place in the
     index for each record.  The expression can be any valid CA-Clipper
     expression (including a UDF) with a value whose type is character,
     date, logical or numeric.

     TO <cIndex> identifies the index filename to create.  This
     clause is used to create a single .ntx or .idx index.  If no extension
     is specified, .ntx or .idx is used, depending on the RDD used to open
     the table in the current work area.

     TAG <cTagName> specifies the name of a new tag for a compound
     index (.cdx).  The name can be up to 10 characters long with the
     same constraints as field names.

     TO <cCDXName> specifies the .cdx in which the specified tag is to
     be created.  If TO <cCDXName> is not specified, the new tag is
     added to the structural .cdx file. The specified or structural .cdx
     file is created if the tag does not already exist.

     FOR <lCondition> is an expression that returns a logical value
     (.T. or .F.) to determine whether or not a key is to be included in
     the index.  Any valid CA-Clipper expression (including a UDF) can be
     used.

     WHILE <lCondition> specifies another condition that each record must
     meet.  As soon as a record is encountered that causes the condition to
     fail, the command terminates.  If a WHILE clause is specified, the
     table is processed in the controlling index order.

     ASCENDING (default) specifies that the index is to be built in
     ascending order.

     DESCENDING specifies that the index is to be built in descending order.
     Note:  DESCENDING is not supported for NONCOMPACT .idx files.

     UNIQUE specifies that only unique keys are to be included in the index.
     If two or more records have the same key value, only the first record
     encountered will be included.  When the index is updated, only unique
     keys will be added.

     NONCOMPACT builds a non-compact FoxPro-compatible .idx file.
     This index type is faster for a smaller number of records since your
     PC does not need to compress and decompress index keys when building
     and accessing the index.  If this is not specified, compact .idx is
     assumed.  Note: DESCENDING is not supported for NONCOMPACT .idx files.

     The ADDITIVE clause tells the Advantage RDD not to close any
     previously opened indexes before creating the new index.  The index
     order will remain unchanged when indexing is complete.

     CUSTOM is used to create an empty index. This is useful if you wish
     to build your own custom index using AX_KeyAdd() and AX_KeyDrop().
     If CUSTOM is specified, then an empty index or tag is created.
     Otherwise, the index or tag is created normally (default).

     CA-Clipper <scope> clauses allow for record scoping when building an
     index.  One of the following scope clauses may be used to select a
     portion of the table from which to build the index:

        ALL              processes all records in the table.  This is
                         the default.
        NEXT <nRecs>     processes the current record and the specified
                         number of records.
        RECORD <nRecNum> processes the specified record.
        REST             processes all records from the current record to
                         the end of the file.

     EVAL <lCondition> is an expression (usually a UDF) that is evaluated
     for each record indexed. This allows the program to display some
     type of completion/process meter without having the UDF embedded in
     the index expression. Indexing stops if the EVAL condition returns
     a (.F.).

     The evaluations take place at record 1, 2, etc. or the set EVERY
     parameter (see below). The record number can be tested within your
     UDF using RecNo().

     The EVAL clause commonly contains UDFs to display the status of
     index creation. The UDF is stored on the client while the index
     building occurs on the server. If the EVAL condition is specified,
     the clause is evaluated every 2 seconds on the client. Advantage
     updates the internal record number and the number of keys included
     so far. If the functions RecNo() and/or AX_KeysIncluded() are used,
     the correct results are returned.

     Note:  When using EVAL, CA-Clipper indicates the percentage based
     on how many records have been extracted from the table to
     sort. CA-Clipper extracts a block of records, sorts it, and repeats
     this process until all records in the table have been extracted.
     Once the blocks are sorted, the index is built. Thus, the
     extraction/sorting of records is more significant than the build
     time. Advantage, on the other hand, has shorter record extraction
     times relative to the build. It is possible that when the EVAL
     clause returns a FALSE to abort an index build, it may take longer
     to abort the index build than expected. If the extraction is done,
     the build continues. Otherwise, if a FALSE is returned during key
     extraction, an empty index is created.

     EVERY <nRecs> is used to specify how often the EVAL expression will
     be evaluated. <nRecs> is a numeric value representing the number
     of records to be processed before the EVAL expression is evaluated.
     If this is specified and if the index is built on the server,
     Advantage ignores the record count. Instead, the status of the EVAL
     is returned every 2 seconds.

     Note:  The WHILE, EVAL, USECURRENT and CA-Clipper <scope> clauses
     are transient. They are not stored in the index file and are not used
     for index updates and REINDEXing purposes.

 Description

     SUBINDEX allows you to create an index based on the keys in another
     index, rather than all of the records in a table.  The index from
     which the subindex is created is usually a conditional index.  This
     allows for a subset of the records in a table to be built
     into a new index.

     Functionality of SUBINDEX is identical to the INDEX ON command. All
     rules of building INDEXes applies to SUBINDEXES as well.

     Note:  If a SUBINDEX is being built on the server, any SCOPES that
     exist on the controlling index will not be obeyed.  Knowledge of
     SCOPEs is only on the client, not on the server.

 Example

     #include "DBFCDXAX.CH"

     USE test VIA "DBFCDXAX"

     // Create STATE.IDX to view only California records
     INDEX ON empid TO state FOR state == "CA"

     // Create subindex on last names of customers in California
     SUBINDEX ON last TO temp