Specifies a set of records to preselect for
a DO or REPEAT block.
Syntax
PRESELECT
[ EACH | FIRST | LAST ]record-phrase
[ , [ EACH | FIRST | LAST ] record-phrase ]...
[[ BREAK ]
{ BY expression[ DESCENDING ]
| COLLATE ( string , strength[ , collation] ) [ DESCENDING ]
}...]
|
-
[ EACH | FIRST | LAST ]record-phrase
- Goes through a table, selecting records that meet the criteria
you specify in record-phrase. PRESELECT creates
a temporary index that contains pointers to each of the preselected
records in the database table. Then you can use other statements,
such as FIND NEXT, within the block to process those records.
The record-phrase option
identifies the criteria to use when preselecting records. Following
is the syntax for the record-phrase:
{record [field-list]}
[constant]
[[ LEFT ] OUTER-JOIN ]
[ OF table ]
[ WHERE expression ]
[ USE-INDEX index ]
[ USING [ FRAME frame ]field
[ AND [ FRAME frame ]field]...
]
[ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
[ NO-PREFETCH ]
|
Specifying multiple occurrences of record-phrase preselects
the tables using an inner join. Also, any sorting you specify applies
to all the tables. If you then do a FIND on the last table in the
PRESELECT list, the AVM reads records into the buffers for all of
the tables in the list.
For more information on record-phrase and inner
joins, see the Record phrase reference entry.
- BREAK
- When used in combination with the FIRST function, LAST function,
FIRST-OF function, and LAST-OF function, BREAK indicates that subgroups
are used for aggregation. If you use BREAK, you must also use BY.
- BY expression[ DESCENDING ]
- Sorts the preselected records by the value of expression.
If you do not use the BY option, PRESELECT sorts the records in
order by the index used to extract the records. The DESCENDING option
sorts the records in descending order (not in the default ascending
order).
- COLLATE ( string , strength[ , collation] ) [ DESCENDING ]
- Generates the collation value of a string after applying a particular strength,
and optionally, a particular collation. The DESCENDING option sorts
the records in descending order (not in default ascending order).
- string
- A CHARACTER expression that evaluates to the string whose collation
value you want to generate.
- strength
- A CHARACTER expression that evaluates to an ABL comparison strength
or an International Components for Unicode (ICU) comparison strength.
The
ABL comparison strengths include:
- RAW — Generates
a collation value for the string based on its binary value.
- CASE-SENSITIVE — Generates a case-sensitive collation value
for the string based on a particular collation. If you specify this strength
with an ICU collation, the AVM applies the ICU TERTIARY strength.
- CASE-INSENSITIVE — Generates a case-insensitive collation
value for the string based on a particular collation. If you specify
this strength with an ICU collation, the AVM applies the ICU SECONDARY strength.
- CAPS — Generates a collation value for the string based
on its binary value after converting any lowercase letters in the
string to uppercase letters, based on the settings of the Internal
Code Page (-cpinternal) and Case Table (-cpcase)
startup parameters.
The ICU comparison strengths include:
- PRIMARY — Generates a collation value for the base characters
in the string.
- SECONDARY — Generates a collation value for the base characters
and any diacritical marks in the string.
- TERTIARY — Generates a case-sensitive collation value
for the base characters and any diacritical marks in the string.
- QUATERNARY — Generates a case-sensitive collation value
for the base characters and any diacritical marks in the string,
and distinguishes words with and without punctuation. ICU uses this
strength to distinguish between Hiragana and Katakana when applied
with the ICU-JA (Japanese) collation. Otherwise, it is the same
as TERTIARY.
Note: Use ICU comparison strengths
only with ICU collations.
- collation
- A CHARACTER expression that evaluates to the name of an ABL collation
table or ICU collation. If collation does not
appear, COLLATE uses the collation table of the client.
The AVM
reports an error and stops execution if one of the following occurs:
- strength does not evaluate to a valid value.
- collation does not evaluate to a collation
table residing in the convmap.cp file.
- collation evaluates to a collation table
that is not defined for the code page corresponding to the -cpinternal startup
parameter.
Examples
To
process a multi-table collection gathered by the PRESELECT option, use
the last table named in the collection when you want to read the
selected records. The AVM then automatically retrieves records from
the other tables.
r-presl1.p
REPEAT PRESELECT EACH Order, Customer OF Order, EACH OrderLine OF Order
BY Order.OrderDate BY Order.CustNum BY OrderLine.ItemNum:
FIND NEXT OrderLine.
DISPLAY Order.OrderDate Order.CustNum Customer.Name OrderLine.ItemNum.
END.
|
The PRESELECT option in this example selects
the logically joined record that consists of Order, OrderLine, and
Customer, and makes all of these records available in the REPEAT
block. Usually you perform more complex processing within the PRESELECT
block.
If, within a PRESELECT block, you find a record using
the ROWID of that record, the AVM disregards any other selection
criteria you applied to the PRESELECT. For example, suppose the
ROWID of Order number 4 is stored in the variable ord-rowid:
DO PRESELECT EACH Order NO-LOCK WHERE Order.OrderNum > 5:
FIND FIRST Order NO-LOCK WHERE ROWID(Order) = ord-rowid.
DISPLAY Order.
END.
|
In this example, the AVM finds and displays
order number 4 even though the selection criteria specifies that
the order number must be greater than 5. The ROWID always overrides
other selection criteria. Furthermore, if you use FIND...WHERE ROWID(record)
=..., the index cursor is not reset in the preselected list. That
is, even if record ROWID(record) is in the preselected
list, FIND NEXT does not find the record that follows it
in the preselected list.
