Sheerpower®
A Guide to the Sheerpower Language
Previous Contents Index

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 table---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-15 SORT within EXTRACT TABLE 

  open table cl: name 'sheerpower:samples\client' 
  extract table 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 table 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-16 SORT within EXTRACT TABLE - ASCENDING or DESCENDING 

  open table cl: name 'sheerpower:samples\client' 
  extract table 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 table 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.

15.7.4 FOR EACH/NEXT

FORMAT:


        FOR EACH table_name 
                ---      
                ---  block of code 
                --- 
        NEXT table_name

EXAMPLE:

Example 15-17 FOR EACH ... NEXT Table 

  open table cl: name 'sheerpower:samples\client' 
  extract table cl 
    include cl(state) = 'CA' 
    exclude cl(phone)[1:3] = '619' 
    sort ascending by cl(last) 
  end extract
  print 'List of California Clients' 
  print
  for each cl 
    print cl(first); ' '; cl(last), cl(phone) 
  next cl 
  close table cl 
  end
 
 
List of California Clients 
 
Dale Derringer      (818) 223-9014 
Earl Errant         (408) 844-7676

DESCRIPTION:

The FOR EACH statement can be used to execute a block of code for each record in the extract list. This allows for manipulation of table information in programs.

The FOR EACH statement begins a loop that executes a block of code for each record in the extract list. table_name is the table name associated with the table. NEXT table_name marks the end of the loop.

The REPEAT, ITERATE and EXIT FOR statements can be used in the FOR EACH loop.

15.7.5 EXTRACT TABLE: KEY

FORMAT:


        EXTRACT TABLE table_name: KEY field = expr1 [TO expr2] 
                  --- 
                  ---  block of code 
                  --- 
        END EXTRACT

table_name is the name associated with the table. field is the name of the field that contains the key. expr is an expression that tells what key(s) to extract. Sheerpower evaluates the expression, checks the table's index for records with matching keys, and extracts these records (if any records are found).

KEY Option

The KEY option includes records using the record's key. The key is a field which has an index for fast access. The key option can considerably speed up extractions.

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.

For example, we have a table with the following client information and the ID field is a key field: 


        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|

In the program below, the KEY option is used to extract the client with the ID number 80561.

EXAMPLE:

Example 15-18 KEY Option in EXTRACT TABLE 

  open table cl: name 'sheerpower:samples\client' 
  extract table cl: key id = 80561 
    print 'Client:', 
    print cl(first); ' '; cl(last), cl(id) 
  end extract
  close table cl 
  end
 
 
Client:            Dale Derringer        80561

TO expr Option

Records can be extracted with keys in a certain range with the TO option. expr1 is the lowest key to check. expr2 is the highest. Sheerpower extracts any records whose keys are within the range specified.

EXAMPLE:

Example 15-19 Extract a Range of Keys - TO expr Option 

  open table cl: name 'sheerpower:samples\client' 
  input 'Enter the lowest ID to check': lowest 
  input 'Enter the highest ID to check': highest 
  extract table cl: key id = lowest to highest
    print cl(id); tab(10); cl(last) 
  end extract
  close table cl 
  end
 
 
Enter the lowest ID to check? 80540 
Enter the highest ID to check? 80570 
80542    Brock 
80543    Cass 
80561    Derringer

15.7.6 EXTRACT TABLE, FIELD: PARTIAL KEY

FORMAT:


        EXTRACT TABLE table_name, FIELD field_expr: PARTIAL KEY str_expr

EXAMPLE:

Example 15-20 PARTIAL KEY Option in EXTRACT STRUCTURE 

  open table cl: name 'sheerpower:samples\client' 
  extract table cl, field last: partial key 'Rod' 
  end extract
  print 'List of clients with last name starting with Rod' 
  print
  for each cl 
    print cl(first); ' '; cl(last) 
  next cl 
  close table cl 
  end
 
 
Homero Rodrigues

DESCRIPTION:

The PARTIAL KEY option will search in the EXTRACT TABLE for part of a key value.

The above example program creates an extract list containing only those clients with a last name starting with "ROS".

Below is a table with the following client information with the ID as a key field: 


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

15.7.7 CANCEL EXTRACT

FORMAT:


        CANCEL EXTRACT

Previous Next Contents Index