SheerPower® 4GL
A Guide to the SheerPower Language
Previous Contents Index

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.

15.7.4 FOR EACH/NEXT

FORMAT:


        FOR EACH struc_name 
                ---      
                ---  block of code 
                --- 
        NEXT struc_name

EXAMPLE:

Example 15-15 FOR EACH ... NEXT structure 

  open structure cl: name 'sheerpower:samples\client' 
  extract structure 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 structure 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 structure information in programs.

The FOR EACH statement begins a loop that executes a block of code for each record in the extract list. struc_name is the structure name associated with the structure. NEXT struc_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 STRUCTURE: KEY

FORMAT:


        EXTRACT STRUCTURE struc_name: KEY field = expr1 [TO expr2] 
                  --- 
                  ---  block of code 
                  --- 
        END EXTRACT

struc_name is the structure name associated with the structure. 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 structure'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 structure 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-16 KEY option in EXTRACT STRUCTURE 

  open structure cl: name 'sheerpower:samples\client' 
  extract structure cl: key id = 80561 
    print 'Client:', 
    print cl(first); ' '; cl(last), cl(id) 
  end extract
  close structure 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.

Previous Next Contents Index