[<<Previous Entry] [^^Up^^] [Next Entry>>] [Menu] [About The Guide]
 INDEX
 Create an index file
------------------------------------------------------------------------------
 Syntax

     INDEX ON <expKey> [TAG <cOrderName>] [TO <cOrderBagName>]
        [FOR <lCondition>] [ALL]
        [WHILE <lCondition>] [NEXT <nNumber>]
        [RECORD <nRecord>] [REST]
        [EVAL <bBlock>] [EVERY <nInterval>]
        [UNIQUE] [ASCENDING|DESCENDING]
        [USECURRENT] [ADDITIVE]
        [CUSTOM] [NOOPTIMIZE]

     Note:  Although both the TAG and the TO clauses are optional, you
     must specify at least one of them.

 Arguments

     <expKey> is an expression that returns the key value to place in the
     index for each record in the current work area.  <expKey> can be
     character, date, logical, or numeric type.  The maximum length of the
     index key expression is determined by the driver.

     TAG <cOrderName> is the name of the order to be created.
     <cOrderName> can be any CA-Clipper expression that evaluates to a string
     constant.

     TO <cOrderBagName> is the name of a disk file containing one or more
     orders.  The active RDD determines the order capacity of an order bag.
     The default DBFNTX driver only supports single-order bags, while other
     RDDs may support multiple-order bags (e.g., the DBFCDX and DBFMDX
     drivers).  You may specify <cOrderBagName> as the file name with or
     without a path name or extension.  If an extension is not provided as
     part of <cOrderBagName>, CA-Clipper will use the default extension of
     the current RDD.

     Both the TAG and the TO clauses are optional, but you must use at least
     one of them.

     FOR <lCondition> specifies the conditional set of records on which
     to create the order.  Only those records that meet the condition are
     included in the resulting order.  <lCondition> is an expression that may
     be no longer than 250 characters under the DBFNTX and DBFNDX drivers.
     The maximum value for these expressions is determined by the RDD.  The
     FOR condition is stored as part of the order bag and used when updating
     or recreating the index using the REINDEX command.  Duplicate key values
     are not added to the order bag.

     Drivers that do not support the FOR condition will produce an
     "unsupported" error.

     The FOR clause provides the only scoping that is maintained for all
     database changes.  All other scope conditions create orders that do not
     reflect database updates.

     ALL specifies all orders in the current or specified work area.  ALL
     is the default scope of INDEX .

     WHILE <lCondition> specifies another condition that must be met by
     each record as it is processed.  As soon as a record is encountered that
     causes the condition to fail, the INDEX command terminates.  If a WHILE
     clause is specified, the data is processed in the controlling order.
     The WHILE condition is transient (i.e., it is not stored in the file and
     not used for index updates and REINDEXing purposes).  The WHILE clause
     creates temporary orders, but these orders are not updated.

     Drivers that do not support the WHILE condition will produce an
     "unsupported" error.

     Using the WHILE clause is more efficient and faster than using the FOR
     clause.  The WHILE clause only processes data for which <lCondition> is
     true (.T.) from the current position.  The FOR clause, however,
     processes all data in the data source.

     NEXT <nNumber> specifies the portion of the database to process.  If
     you specify NEXT, the database is processed in the controlling order for
     the <nNumber> number of identities.  The scope is transient (i.e., it is
     not stored in the order and not used for REINDEXing purposes).

     RECORD <nRecord> specifies the processing of the specified record.

     REST specifies the processing of all records from the current
     position of the record pointer to the end of file (EOF).

     EVAL <bBlock> evaluates a code block every <nInterval>, where
     <nInterval> is a value specified by the EVERY clause.  The default value
     is 1.  This is useful in producing a status bar or odometer that
     monitors the indexing progress.  The return value of <bBlock> must be a
     logical data type.  If <bBlock> returns false (.F.), indexing halts.

     EVERY <nInterval> is a clause containing a numeric expression that
     modifies the number of times <bBlock> is EVALuated.  The EVERY option of
     the EVAL clause offers a performance enhancement by evaluating the
     condition for every nth record instead of evaluating every record
     ordered.  The EVERY keyword is ignored if you specify no EVAL condition.

     UNIQUE specifies that the key value of each record inserted into the
     order be unique.  Duplicate key values are not added to the order.

     ASCENDING specifies that the keyed pairs be sorted in increasing
     order of value.  If neither ASCENDING nor DESCENDING is specified,
     ASCENDING is assumed.  Although not stored as an explicit part of the
     file, ASCENDING is an implicit file attribute that is understood by the
     REINDEX command.

     Drivers that do not support the ASCENDING condition will produce an
     "unsupported" error.  The following keywords are new to CA-Clipper 5.3.

     DESCENDING specifies that the keyed pairs be sorted in decreasing
     order of value.  Using this keyword is the same as specifying the
     DESCEND() function within <expKey>, but without the performance penalty
     during order updates.  If you create a DESCENDING index, you will not
     need to use the DESCEND() function during a SEEK.  DESCENDING is an
     attribute of the file, where it is stored and used for REINDEXing
     purposes.

     Drivers that do not support the DESCENDING condition will produce an
     "unsupported" error.

     USECURRENT specifies that only records in the controlling order--and
     within the current range as specified by ORDSETSCOPE()--will be included
     in this order.  This is useful when you have already created a
     conditional order and want to reorder the records which meet that
     condition, and/or to further restrict the records meeting a condition.
     If not specified, all records in the database file are included in the
     order.

     ADDITIVE specifies that any open orders should remain open.  If not
     specified, all open orders are closed before creating the new one.
     Note, however, that the production index file is never closed.

     CUSTOM specifies that a custom built order will be created for RDDs
     that support them.  A custom built order is initially empty, giving you
     complete control over order maintenance.  The system does not
     automatically add and delete keys from a custom built order.  Instead,
     you explicitly add and delete keys using ORDKEYADD() and ORDKEYDEL().
     This capability is excellent for generating pick lists of specific
     records and other custom applications.

     NOOPTIMIZE specifies that the FOR condition will not be optimized.
     If NOOPTIMIZE is not specified, the FOR condition will be optimized if
     the RDD supports optimization.

 Description

     The INDEX command adds a set of keyed pairs, ordered by <expKey> to a
     file specified by <cOrderBagName> using the database open in the current
     work area.

     In RDDs that support production or structural indexes (e.g., DBFCDX,
     DBFMDX), if you specify a tag but do not specify an order bag, the tag
     is created and added to the order bag.  If no production or structural
     index exists, it will be created and the tag will be added to it.

     When using RDDs that support multiple order bags, you must explicitly
     SET ORDER (or ORDSETFOCUS()) to the desired controlling order.  If you
     do not specify a controlling order, the data file will be viewed in
     natural order.

     If <cOrderBagName> does not exist, it is created in accordance with the
     RDD in the current or specified work area.

     If <cOrderBagName> exists and the RDD specifies that order bags can only
     contain a single order, <cOrderBagName> is erased and the new order is
     added to the order bag and to the order list in the current or specified
     work area.

     If <cOrderBagName> exists and the RDD specifies that order bags can
     contain multiple tags, <cOrderName> is created if it does not already
     exist; otherwise, <cOrderName> is replaced in <cOrderBagName> and the
     order is added to the order list in the current or specified work area.

     ASCENDING or DESCENDING specifies the sequence of keyed pairs in the order.
     If neither clause is specified, the default is ASCENDING.

     If you specify the UNIQUE clause, the resulting order will contain only
     unique records.  Some RDDs may do this by only including record
     references to a key value once.  Others may produce a runtime
     recoverable error as a non-unique key insertion is attempted.

     The EVAL clause lets you specify a code block to be evaluated as each
     record is placed in the order.  The EVERY clause lets you modify how
     often <bBlock> is called.  Instead of evaluation as each record is
     placed in the order, evaluation only occurs as every <nInterval> records
     are placed in the order.

     The INDEX command accepts certain clauses that let the user create
     conditional and partial orders.  Some orders are intended to be
     maintained across the application, others are considered "temporary"
     orders.

     The FOR clause provides the only order scoping that is permanent and can
     be maintained across the life of the application.  The string passed as
     the FOR condition is stored within the order for later use in
     maintaining the order.  Though only accessing part of a database, orders
     created using this clause exist as long as the database is active.  The
     FOR clause lets you create maintainable scoped orders.

     The WHILE, NEXT, REST and RECORD clauses process data from the current
     position of the database cursor in the default or specified work area.
     If you specify these clauses, the order list remains open and the active
     order is used to organize the database while it is being created.  These
     clauses let you create temporary (non-maintainable) orders.  Orders
     created using these clauses contain records in which <lCondition> is
     true (.T.) at the location of the record pointer.

 Notes

     RDD support:  Not all RDDs support all aspects of the INDEX command.
     See the "Replaceable Database Driver Architecture" chapter in the
     Drivers Guide for details on a particular RDD.

 Examples

     .  The following example creates a simple order (index) based on
        one field (Acct):

        USE Customer NEW
        INDEX ON Customer->Acct TO CuAcct

     .  This example creates a conditional order (index) based on a
        FOR clause.  This index will contain only records whose field
        TransDate contains a date greater than or equal to January 1, 1995:

        USE Invoice NEW
        INDEX ON Invoice->TransDate      ;
           TO InDate      ;
           FOR ( Invoice->TransDate >= CTOD( "01/01/95" ) )

     .  This example creates an order in a multiple-order bag (i.e., a
        tag in
        an index that can support multiple tags in an index file):

        USE Customer NEW
        INDEX ON Customer->Acct TAG CuAcct TO Customer

     .  The following example creates an order that calls a routine,
        MyMeter, during its creation:

        #define MTR_INCREMENT   10

        USE Customer NEW
        INDEX ON Customer->Acct TO CuAcct EVAL ;
              {|| MYMETER() } EVERY MTR_INCREMENT

        FUNCTION MYMETER()

           STATIC nRecsDone := 0

           nRecsDone := += MTR_INCREMENT
           ? ( nRecsDone/LASTREC() ) * 100

           RETURN (.T.)

 Files   Library is CLIPPER.LIB.

See Also: CLOSE DBCREATEIND() DBORDERINFO() DBREINDEX()
This page created by ng2html v1.05, the Norton guide to HTML conversion utility. Written by Dave Pearson