SheerPower® 4GL
A Guide to the SheerPower Language


Previous Contents Index

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-18 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.

EXAMPLE:

Example 15-19 Extract a range of keys - TO expr option

  open structure cl: name 'sheerpower:samples\client' 
  input 'Enter the lowest ID to check': lowest 
  input 'Enter the highest ID to check': highest 
  extract structure cl: key id = lowest to highest
    print cl(id); tab(10); cl(last) 
  end extract
  close structure 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 STRUCTURE, FIELD: PARTIAL KEY

FORMAT:


        EXTRACT STRUCTURE struc_name, FIELD field_expr: PARTIAL KEY str_expr 

EXAMPLE:

Example 15-20 PARTIAL KEY option in EXTRACT STRUCTURE

  open structure cl: name 'sheerpower:samples\client' 
  extract structure 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 structure cl 
  end
 
 
Homero Rodrigues 

DESCRIPTION:

The PARTIAL KEY option will search in the EXTRACT STRUCTURE 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 structure 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 

EXAMPLE:

Example 15-21 CANCEL EXTRACT

  open structure cl: name 'sheerpower:samples\client' 
  extract structure cl 
    print 'Client: '; cl(last) 
    line input 'Press enter to continue': z$ 
    if  _exit  then cancel extract
  end extract
  print 'Records extracted:'; _extracted
  close structure cl 
  end
 
 
Client: Smith 
Press enter to continue? EXIT 
Records extracted: 0 

DESCRIPTION:

CANCEL EXTRACT cancels the current extract of a record and transfers control to the next statement after the END EXTRACT statement.

This statement can only be used within an EXTRACT block---that is, between an EXTRACT STRUCTURE and an END EXTRACT pair of statements.

15.7.8 EXIT EXTRACT

FORMAT:


        EXIT EXTRACT 

EXAMPLE:

Example 15-22 EXIT EXTRACT

  open structure cl: name 'sheerpower:samples\client' 
  extract structure cl 
    print 'Client: '; cl(last) 
    line input 'Press enter to continue': z$ 
    if  _exit  then exit extract
  end extract
  print 'Records extracted:'; _extracted 
  end
 
 
Client: Smith 
Press enter to continue? <ENTER> 
Client: Kent 
Press enter to continue? EXIT 
Records extracted: 1 

DESCRIPTION:

EXIT EXTRACT passes control to the corresponding END EXTRACT statement, performs final sorting (if any), and creates the extracted collection.

15.7.9 REEXTRACT STRUCTURE

FORMAT:


        REEXTRACT STRUCTURE struc_name 
                  --- 
           [INCLUDE | EXCLUDE] cond_expr... 
           [SORT [ASCENDING | DESCENDING] BY expression... 
                  --- 
        END EXTRACT 

EXAMPLE:

Example 15-23 REEXTRACT STRUCTURE ... END EXTRACT

  open structure cl: name 'sheerpower:samples\client', access input
  extract structure cl 
    include cl(state) = 'CA' 
  end extract
  reextract structure cl 
    exclude cl(phone)[1:3] <> '619' 
    sort ascending by cl(last) 
  end extract
  print 'List of California Clients in Area Code 619' 
  for each cl 
    print cl(first); ' '; cl(last), cl(phone) 
  next cl 
  close structure cl 
  end
 
 
List of California Clients in Area Code 619 
Cathy Cass          (619) 743-8582 
Paul Johnson        (619) 489-5551 
Keith Kent          (619) 967-5021 
Pete Porter         (619) 778-6709 
Wayne Waters        (619) 564-1231 

DESCRIPTION:

REEXTRACT STRUCTURE can be used to do a second extract on a list of structure records you previously extracted. This allows for increasingly specific records to be chosen through a series of REEXTRACTs.

REEXTRACT does an extract on the list of records previously extracted. struc_name is the structure name associated with an open structure.

END EXTRACT marks the end of the REEXTRACT construct. REEXTRACT operates the same as EXTRACT. However, REEXTRACT operates on a previously extracted list.

Note on EXTRACT

Extract operations by key cannot be performed with REEXTRACT.

15.7.10 EXTRACT STRUCTURE: APPEND

FORMAT:


        EXTRACT STRUCTURE struc_name: APPEND 

EXAMPLE:

Example 15-24 APPEND option in EXTRACT STRUCTURE

  open structure detail: name 'sheerpower:samples\detail' 
  set structure detail: extracted 0 
  extract structure detail, field lineid : & 
      key '10301001' to '10302000', append
    sort by detail(prodnbr) 
    sort by detail(invnbr) 
  end extract
  extract structure detail, field lineid : & 
      key '10311001' to '10312000', append
    sort by detail(prodnbr) 
    sort by detail(invnbr) 
  end extract
  print 'Prod'; tab(7); 'Line ID'; tab(17); 'Quantity' 
  for each detail 
    print detail(prodnbr); tab(7); detail(lineid); & 
        tab(17); detail(qty) 
  next detail 
  end
 
 
Prod  Line ID   Qty 
22800 10301-002      2 
22800 10301-004      1 
22800 10301-006      2 
24100 10311-003      1 
24200 10301-003      1 
24200 10311-009      1 
28400 10311-001      2 
28800 10301-009      2 
28800 10311-002      9 
28800 10311-005      1 
28800 10311-006      1 
31020 10301-005      1 
31040 10311-010      2 
31150 10301-001      1 
31150 10301-008      8 
31150 10311-004      1 
31150 10311-008      1 
33090 10301-007      2 
33090 10311-007      1 


Previous Next Contents Index