DESCRIPTION:

DELETE STRUCTURE deletes the current record from the specified structure. struc_name is the structure name associated with the structure that the record is going to be deleted from.

15.6 LOCK | UNLOCK STRUCTURE

FORMAT:

[LOCK | UNLOCK] STRUCTURE struc_name

EXAMPLE:

Example 15-9 LOCK/UNLOCK STRUCTURE

open structure cl: name 'sheerpower:samples\client', access outin extract structure cl include cl(state) = 'CA' end extract for each cl print print cl(first); ' '; cl(last) lock structure cl // give us exclusive access line input default cl(phone), prompt 'Enter new phone ': phone$ if _exit then exit for cl(phone) = phone$ unlock structure cl // put the record out to disk // and release it next cl close structure cl end Keith Kent Enter new phone 6199675021 Paul Johnson Enter new phone EXIT

DESCRIPTION:

LOCK/UNLOCK STRUCTURE can be used to lock or unlock the data record currently being accessed. SheerPower automatically locks and unlocks data when you work with it. LOCK and UNLOCK override SheerPower's automatic locking features allowing you to do it manually.

LOCK STRUCTURE locks the data currently being accessed, giving the program exclusive access to the current record. No one else can access the data until it is unlocked.

UNLOCK STRUCTURE unlocks the current record or data. The record is put to disk (if needed) and can again be accessed by others.

struc_name is the structure name associated with the structure.

FORMAT:

EXTRACT STRUCTURE struc_name --- block of code [INCLUDE | EXCLUDE] cond_expr [SORT [ASCENDING | DESCENDING] BY expression] --- END EXTRACT

EXAMPLE:

Example 15-10 EXTRACT STRUCTURE - Extracting records from a structure

open structure cl: name 'sheerpower:samples\client' print 'List of Clients' print extract structure cl print cl(first); ' '; cl(last), cl(phone) end extract close structure cl end List of Clients Earl Errant (408) 844-7676 Al Abott (202) 566-9892 Bud Brock (218) 555-4322 Cathy Cass (619) 743-8582 Dale Derringer (818) 223-9014 Fred Farmer (305) 552-7872

DESCRIPTION:

When SheerPower does an extract, it examines each record in the structure. For each record, SheerPower executes the code between the EXTRACT STRUCTURE and END EXTRACT statements. For instance, here is a structure with client information:

ID # LAST FIRST CITY STATE PHONE +------+-----------+--------+--------------+--+----------+ |80543 |Cass |Cathy | San Diego |CA|6197438582| |80542 |Brock |Bud | Duluth |MN|2185554322| |80522 |Errant |Earl | Monterey |CA|4088447676| |80561 |Derringer |Dale | Los Angeles |CA|8182239014| |80531 |Abott |Al | New York |NY|2025669892| |80573 |Farmer |Fred | Miami |FL|3055527872|

EXTRACT creates a list of clients. The above program example extracts information from each record in the structure and displays each client's name and phone number.

When SheerPower does an extract, it examines each record of the structure in order. If the KEY option is specified, only those records with a key matching the KEY expression are examined. For each examined record, the following is done:

1. Each INCLUDE and EXCLUDE statement is checked in turn. The examined record is not extracted if an INCLUDE statement evaluates to FALSE, or an EXCLUDE statement evaluates to TRUE.
2. If any SORT specifications are given, a sort key is built using the SORT expression as the key. If no SORT specifications are given, the record is immediately extracted.
3. When all records have been examined, the sort keys, if any, are sorted.

SheerPower then builds a list of extracted records. This list can be accessed later with a FOR EACH loop. You can have up to 16 sort keys and 32 extract criteria.

15.7.1 INCLUDE Statement

FORMAT:

INCLUDE cond_expr

EXAMPLE:

Example 15-11 INCLUDE within EXTRACT STRUCTURE

open structure cl: name 'sheerpower:samples\client' extract structure cl include cl(state) = 'CA' end extract print 'List of California Clients' print for each cl print cl(first); ' '; cl(last), cl(state) next cl close structure cl end List of California Clients Keith Kent CA Paul Johnson CA Wayne Waters CA Earl Errant CA Cathy Cass CA Pete Porter CA Dale Derringer CA

DESCRIPTION:

The INCLUDE statement includes records depending on the value of a conditional expression.

cond_expr is a conditional expression that SheerPower evaluates. If the expression is TRUE, SheerPower includes the record in the extract list. If the expression is FALSE, SheerPower excludes the record from the list. For example, the program above creates an extract list containing only those clients located in California.

NOTE: The conditional expression must match the field's data type. For instance, if the field has a CHARACTER data type, the expression must be a string expression.

15.7.2 EXCLUDE Statement

FORMAT:

EXCLUDE cond_expr

EXAMPLE:

Example 15-12 EXCLUDE within EXTRACT STRUCTURE

open structure cl: name 'sheerpower:samples\client' extract structure cl exclude cl(phone)[1:3] = '619' end extract print 'List of Clients' print for each cl print cl(first); ' '; cl(last), cl(phone) next cl close structure cl end List of Clients Earl Errant (408) 844-7676 Al Abott (202) 566-9892 Bud Brock (218) 555-4322 Dale Derringer (818) 223-9014 Fred Farmer (305) 552-7872

DESCRIPTION:

The EXCLUDE statement excludes records from the extract list, depending on the value of a conditional expression.

cond_expr is a conditional expression. SheerPower evaluates this expression. If the expression is TRUE, SheerPower excludes the current record from the extract list. For example, the program above creates an extract list of all the clients in the client structure---except those with an area code of 619.

NOTE: The conditional expression must match the field's data type. For instance, if the field has a CHARACTER data type, the expression must be a string expression.

15.7.3 SORT Statement

FORMAT:

SORT [ASCENDING | DESCENDING] BY expr

EXAMPLE:

Example 15-13 SORT within EXTRACT STRUCTURE

open structure cl: name 'sheerpower:samples\client' extract structure cl sort ascending by cl(last) end extract print 'List of Clients' print for each cl print cl(first); ' '; cl(last), cl(phone) next cl close structure cl end List of Clients Al Abott (202) 566-9892 Bud Brock (218) 555-4322 Cathy Cass (619) 743-8582 Dale Derringer (818) 223-9014 Earl Errant (408) 844-7676 Fred Farmer (305) 552-7872

DESCRIPTION:

The SORT statement sorts the records in an extract list in either ASCENDING or DESCENDING order. expr is an expression whose value determines how to order the list. SheerPower evaluates the expression for each record and stores the value. When all the records have been extracted, SheerPower orders the list according to these values.

You can sort in either ASCENDING or DESCENDING order. ASCENDING creates a list in ascending order (lowest to highest). DESCENDING creates a list in descending order (highest to lowest). The default is ascending order. String values are sorted according to the ASCII character set.

SheerPower does sorts in order. Therefore, you can use multiple sorts to order the list more and more specifically. For example, the following program creates a list of clients. The clients are sorted first by state and within each state by last name.

Example 15-14 SORT within EXTRACT STRUCTURE - ASCENDING or DESCENDING

open structure cl: name 'sheerpower:samples\client' extract structure cl sort ascending by cl(state) sort ascending by cl(last) end extract print 'List of Clients' print for each cl print cl(last); ', '; cl(first), cl(state) next cl close structure cl end List of Clients Cass, Cathy CA Derringer, Dale CA Errant, Earl CA Farmer, Fred FL Brock, Bud MN Abott, Al NY

When you sort fields that are filled with nulls (no data was ever stored in them), the fields are sorted as though they were space-filled.