UPDATE

Update current database file from another database file

Syntax

      UPDATE FROM <xcAlias>
            ON <expKey> [RANDOM]
            REPLACE <idField> WITH <exp>
            [, <idField2> WITH    <exp2>...]

Arguments

FROM <xcAlias> specifies the alias of the work area used to update records in the current work area. This argument may be specified either as a literal file name or as a character expression enclosed in parentheses.

ON <expKey> specifies the expression that defines matching records in the FROM work area.

REPLACE <idField> specifies a field in the current work area to replace with a new value.

WITH <exp> specifies the value to replace into the current field. You must reference any field contained in the FROM work area with the correct alias.

RANDOM allows records in the FROM database file to be in any order. If this option is specified, the current database file must be indexed on <expKey>.

Description

UPDATE is a database command that replaces fields in the current work area with values from another work area based on the specified key expression. UPDATE is designed to update only current work area records based on a one-to-one or one-to-many relation with the FROM work area. This means that UPDATE can only update records in the current work area with unique key values. When there is more than one instance of a key value, only the first record with the key value is updated. The FROM work area, however, can have duplicate key values.

There are two formulations of the command depending on whether the FROM work area records are sorted or indexed on <expKey> or not. If RANDOM is not specified, both the current work area and the FROM work area must be indexed or sorted in <expKey> order. If RANDOM is specified, the current work area must be indexed by <expKey>, but the FROM work area records can be in any order.

To use UPDATE in a network environment, the current database file must be locked with FLOCK() or USEed EXCLUSIVEly. The FROM database file may be used in any mode. Refer to the “Network Programming” chapter in the Programming and Utilities Guide for more information.

Notes

. Deleted records: If DELETED is OFF, deleted records in both source files are processed. Records in the file being updated retain their deleted status and are not affected by the deleted status of records in the FROM file. If DELETED is ON, however, no deleted records are processed from either source file.

Examples

      .  This example UPDATEs the Customer database file with
         outstanding invoice amounts:

      USE Invoices NEW
      USE Customer INDEX Customer NEW
      UPDATE FROM Invoices ON Last;
         REPLACE Owed WITH Owed + Invoices->Amount RANDOM

Seealso

DBCREATEIND(), INDEX, JOIN, REPLACE, SET UNIQUE*, SORT

TOTAL

Summarize records by key value to a database (.dbf) file

Syntax

      TOTAL ON <expKey> [FIELDS <idField list>]
            TO <xcDatabase>
            [<scope>] [WHILE <lCondition>] [FOR <lCondition>]

Arguments

ON <expKey> defines the group of records that produce a new record in the target database file. To make the summarizing operation accurate, the source database file should be INDEXed or SORTed on this expression.

FIELDS <idField list> specifies the list of numeric fields to TOTAL. If the FIELDS clause is not specified, no numeric fields are totaled. Instead each numeric field in the target file contains the value for the first record matching the key expression.

TO <xcDatabase> is the name of the target file that will contain the copy of the summarized records. Specify this argument as a literal file name or as a character expression enclosed in parentheses. Unless otherwise specified, TOTAL assumes a .dbf extension.

<scope> is the portion of the current database file to TOTAL. The default is ALL records.

WHILE <lCondition> specifies the set of records meeting the condition from the current record until the condition fails.

FOR <lCondition> specifies the conditional set of records to TOTAL within the given scope.

Description

TOTAL is a database command that sequentially processes the current database file, summarizing records by the specified key value and copying them to a new database file. TOTAL works by first copying the structure of the current database file to <xcDatabase>, except for memo fields. It then sequentially scans the current database file within the specified scope of records. As each record with a unique <expKey> value is encountered, that record is copied to the new database file. The values of numeric fields specified in <idField list> from successive records with the same <expKey> value are added to fields with the same names in <xcDatabase>. Summarization proceeds until a record with a new key value is encountered. The process is then repeated for this record.

Since TOTAL processes the source database file sequentially, it must be INDEXed or SORTed in <expKey> order for the summarization to be correct.

To successfully TOTAL numeric fields, the source numeric fields must be large enough to hold the largest total possible for that numeric field. If not, a runtime error is generated.

Notes

. Deleted source records: If DELETED is OFF, deleted records in the source file are TOTALed. Records in the target <xcDatabase> inherit the deleted status of the first matching record in the source file, just as nontotaled fields inherit their values. If DELETED is ON, however, none of the deleted source records are TOTALed.

Examples

      .  In this example, a database file is TOTALed ON the key
         expression of the controlling index using a macro expression.  When
         the macro expression is encountered, the expression is evaluated and
         the resulting character string is substituted for the TOTAL <expKey>
         argument:

      USE Sales INDEX Branch NEW
      TOTAL ON &(INDEXKEY(0)) FIELDS Amount TO Summary

Seealso

AVERAGE, INDEX, SORT SUM

SORT

Copy to a database (.dbf) file in sorted order

Syntax

      SORT TO <xcDatabase> ON <idField1> [/[A | D][C]]
            [, <idField2> [/[A | D][C]]...]
            [<scope>] [WHILE <lCondition>] [FOR <lCondition>]

Arguments

TO <xcDatabase> is the name of the target file for the sorted records and can be specified either as a literal file name or as a character expression enclosed in parentheses. Unless otherwise specified, the new file is assigned a (.dbf) extension.

ON <idField> is the sort key and must be a field variable.

/[A|D][C] specifies how <xcDatabase> is to be sorted. /A sorts in ascending order. /D sorts in descending order. /C sorts in dictionary order by ignoring the case of the specified character field. The default SORT order is ascending.

<scope> is the portion of the current database file to SORT. The default is ALL records.

WHILE <lCondition> specifies the set of records meeting the condition from the current record until the condition fails.

FOR <lCondition> specifies the conditional set of records to SORT within the given scope.

Description

SORT is a database command that copies records from the current work area to another database file in sorted order. Clipper SORTs character fields in accordance with the ASCII value of each character within the string unless the /C option is specified. This option causes the database file to be sorted in dictionary order–capitalization is ignored. Numeric fields are sorted in numeric order, date fields are sorted chronologically, and logical fields are sorted with true (.T.) as the high value. Memo fields cannot be sorted.

SORT performs as much of its operation as possible in memory, and then, it spools to a uniquely named temporary disk file. This temporary file can be as large as the size of the source database file. Note also that a SORT uses up three file handles: the source database file, the target database file, and the temporary file. In a network environment, you must lock the database file to be SORTed with FLOCK() or USE it EXCLUSIVEly.

Notes

. Deleted source records: If DELETED is OFF, SORT copies deleted records to the target database file; however, the deleted records do not retain their deleted status. No record is marked for deletion in the target file regardless of its status in the source file.

If DELETED is ON, deleted records are not copied to the target database file. Similarly, filtered records are ignored during a SORT and are not included in the target file.

Examples

      .  This example copies a sorted subset of a mailing list to a
         smaller list for printing:

      USE Mailing INDEX Zip
      SEEK "900"
      SORT ON LastName, FirstName TO Invite WHILE Zip = "900"
      USE Invite NEW
      REPORT FORM RsvpList TO PRINTER

Seealso

ASORT(), FLOCK(), INDEX, USE

SET ORDER

Select the controlling order

Syntax

      SET ORDER TO [<nOrder> | [TAG <cOrderName>]
            [IN <xcOrderBagName>]]

Arguments

TAG is an optional clause that provides compatibility with RDDs that access multiple-order order bags. You must use this keyword anytime you specify <cOrderName>.

<cOrderName> is the name of an order, a logical arrangement of a database according to a keyed pair. This order will become the controlling order in the order list. If you specify <cOrderName>, you must use the keyword TAG.

Note: This differs from dBASE and FoxPro where TAG is totally optional.

<nOrder> is the number of the target order in the order list. You may represent the order as an integer or as a character string enclosed in quotes.

IN <xcOrderBagName> is the name of a disk file containing one or more orders. You may specify <xcOrderBagName> as the file name with or without the path name or appropriate extension. If you do not include the extension as part of <xcOrderBagName>, Harbour uses the default extension of the current RDD.

Description

When you SET ORDER TO a new controlling order (index), all orders are properly updated when you either append or edit records. This is true even if you SET ORDER TO 0. After a change of controlling order, the record pointer still points to the same record.

SET ORDER TO 0 restores the database access to natural order, but leaves all orders open. SET ORDER TO with no arguments closes all orders and empties the order list

Though you may use <cOrderName> or <nOrder> to specify the target order, <nOrder> is only provided for compatibility with earlier versions of Harbour. Using <cOrderName> is a surer way of accessing the correct order in the order list.

If you supply <xcOrderBagName>, only the orders belonging to <xcOrderBagName> in the order list are searched. Usually you need not specify <xcOrderBagName> if you use unique order names throughout an application.

To determine which order is the controlling order use the ORDSETFOCUS() function.

In RDDs that support production or structural indices (e.g., DBFCDX), if you specify a tag but do not specify an order bag, the tag is created and added to the index. 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.

SET ORDER can open orders in a network environment instead of the INDEX clause of the USE command. Generally, specify USE, and then test to determine whether the USE succeeded. If it did succeed, open the associated orders with SET ORDER. See the example below.

Examples

      USE Customer NEW
      IF (! NETERR())
         SET ORDER TO Customer
      ENDIF

      SET ORDER TO "CuAcct"         // CuAcct is an Order in Customer

Seealso

INDEX, INDEXORD(), SEEK, SET INDEX, USE

SET UNIQUE

Toggle inclusion of non-unique keys into an index

Syntax

      SET UNIQUE on | OFF | <xlToggle>

Arguments

ON causes index files to be created with a uniqueness attribute.

OFF causes index files to be created without a uniqueness attribute.

<xlToggle> is a logical expression that must be enclosed in parentheses. A value of true (.T.) is the same as ON, and a value of false (.F.) is the same as OFF.

Description

SET UNIQUE is a database command that controls whether indexes are created with uniqueness as an attribute. With UNIQUE ON, new indexes are created including only unique keys. This is the same as creating an index with the INDEX…UNIQUE command.

If, during the creation or update of an unique index, two or more records are encountered with the same key value, only the first record is included in the index. When the unique index is updated, REINDEXed, or PACKed, only unique records are maintained, without regard to the current SET UNIQUE value.

Changing key values in a unique index has important implications. First, if a unique key is changed to the value of a key already in the index, the changed record is lost from the index. Second, if there is more than one instance of a key value in a database file, changing the visible key value does not bring forward another record with the same key until the index is rebuilt with REINDEX, PACK, or INDEX…UNIQUE.

With UNIQUE OFF, indexes are created with all records in the index. Subsequent updates to the database files add all key values to the index independent of the current UNIQUE SETting.

SET UNIQUE is a compatibility command not recommended. It is superseded by the UNIQUE clause of the INDEX command.

Seealso

DBCREATEIND(), INDEX, PACK, REINDEX, SEEK

REINDEX

Rebuild open indexes in the current work area

Syntax

      REINDEX
            [EVAL <lCondition>]
            [EVERY <nRecords>]

Arguments

EVAL <lCondition> specifies a condition that is evaluated either for each record processed or at the interval specified by the EVERY clause. This clause is identical to the EVAL clause of the INDEX command, but must be respecified in order for the reindexing operation to be monitored since the value of <lCondition> is transient.

EVERY <nRecords> specifies a numeric expression that modifies how often EVAL is evaluated. When using EVAL, the EVERY option offers a performance enhancement by evaluating the condition for every nth record instead of evaluating each record reindexed. The EVERY keyword is ignored if no EVAL condition is specified.

Description

REINDEX is a database command that rebuilds all open indexes in the current work area. When the reindexing operation finishes, all rebuilt indexes remain open, order is reset to one, and the record pointer is positioned to the first record in the controlling index. If any of the indexes were created with SET UNIQUE ON, REINDEX adds only unique keys to the index. If any of the indexes were created using a FOR condition, only those key values from records matching the condition are added to the index.

In a network environment, REINDEX requires EXCLUSIVE USE of the current database file. Refer to the “Network Programming” chapter for more information.

Caution! REINDEX does not recreate the header of the index file when it recreates the index. Because of this, REINDEX does not help if there is corruption of the file header. To guarantee a valid index, always use INDEX ON in place of REINDEX to rebuild damaged indexes

Notes

Index key order, UNIQUE status, and the FOR condition are known to the index (.ntx) file and are, therefore, respected and maintained by REINDEX.

Examples

      .  This example REINDEXes the index open in the current work
         area:

         USE Sales INDEX Salesman, Territory NEW
         REINDEX

      .  This example REINDEXes using a progress indicator:

         USE Sales INDEX Salesman, Territory NEW
         REINDEX EVAL NtxProgress() EVERY 10
         FUNCTION NtxProgress
         LOCAL cComplete := LTRIM(STR((RECNO()/LASTREC()) * 100))
         @ 23, 00 SAY "Indexing..." + cComplete + "%"
         RETURN .T.

Seealso

INDEX, PACK, SET INDEX, USE

Descend()

Inverts an expression of string, logical, date or numeric type.

Syntax

      DESCEND( <xExp> ) --> xExpInverted

Arguments

<xExp> is any valid expression.

Returns

Inverted value of the same type as passed.

Description

This function converts an expression in his inverted form. It is useful to build descending indexes.

Examples

      // Seek for Smith in a descending index
      SEEK DESCEND( "SMITH" )

Tests

      DATA->( DBSEEK( DESCEND( "SMITH" ) ) )
      will seek "SMITH" into a descending index.

Compliance

Clipper

Files

Library is rtl

Seealso

INDEX, SEEK

SP_PROGINDEX

PROGINDEX()

  Short:
  ------
  PROGINDEX() Perform an index with a progress box

  Returns:
  --------
  Nil

  Syntax:
  -------
  PROGINDEX(cName,cKey,[lUnique],[lShowCount],[lPause])

  Description:
  ------------
  Pops up a progress bar box with PROGON().

  Performs an index using <cName> as the index name,
  and <cKey> as the index key.

  [lUnique] for index UNIQUE (default False)

  [lShowCount] to show an index count as well as the
  progress bar - default False

  [lPause] if True will pause before removing the box
  with PROGOFF()- default is False.

  Examples:
  ---------

   use customer
   ProgIndex("Eraseme","LNAME",.f.,.t.,.t.)

  Source:
  -------
  S_PROG.PRG

See also : PROGCOUNT(), PROGDISP(), PROGEVAL(), PROGOFF(), PROGON()

---

SP_NKEY

NKEY()

  Short:
  ------
  NKEY() Gets key of an index file

  Returns:
  --------
  <cKey> => index key expression

  Syntax:
  -------
  NKEY(cIndex)

  Description:
  ------------
  Gets key expression of index <cIndex>. <cIndex> is
  the name of the index including extension. The function operates
  differently from .ntx to .ndx.

  Examples:
  ---------
   for i = 1 to nIndexes

       ?"Key for index: "+aInd[i]+" is "+NKEY(aInd[i])

   next

  Warnings:
  ----------
  Uses up a file handle temporarily. Opens up the index
  with low level file functions.

  Notes:
  -------
  NKEY() expects the full path of the index file and if
  no path is passed, will use SET DEFAULT setting

  Source:
  -------
  S_NKEY.PRG

---

 

 

SP_NBR2STR

 NBR2STR()

  Short:
  ------
  NBR2STR() Correctly orders numerics where negative

  Returns:
  --------
  String

  Syntax:
  -------
  NBR2STR(nNumber)

  Description:
  ------------
  Ensure numeric fields are correctly ordered when
  converting to type character and when taking negatives into account.

  This is done by attaching CR, DB or CZ to the end of
  the number to overcome the placement of (-+) in the ASCII scale.

  Examples:
  ---------
   index on LASTNAME+NBR2STR(amount_due) to NEWINDEX

  Source:
  -------
  S_NBR2ST.PRG

---