| Previous | Contents | Index |
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 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
|
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 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
EXTRACT STRUCTURE struc_name, FIELD field_expr: PARTIAL KEY str_expr
|
| 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
|
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|
|
CANCEL EXTRACT
|
| 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
|
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.
EXIT EXTRACT
|
| 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
|
EXIT EXTRACT passes control to the corresponding END EXTRACT statement, performs final sorting (if any), and creates the extracted collection.
REEXTRACT STRUCTURE struc_name
---
[INCLUDE | EXCLUDE] cond_expr...
[SORT [ASCENDING | DESCENDING] BY expression...
---
END EXTRACT
|
| 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
|
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.
Extract operations by key cannot be performed with REEXTRACT. |
15.7.10 EXTRACT STRUCTURE: APPEND
EXTRACT STRUCTURE struc_name: APPEND
|
| 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 |