{ record [ field-list ] }
[ constant ]
[ [ LEFT ] OUTER-JOIN ]
[ OF table ]
[ WHERE expression ]
[ TENANT-WHERE expression [ SKIP-GROUP-DUPLICATES ] ]
[ USE-INDEX index | TABLE-SCAN ]
[ USING [ FRAME frame ] field
[ AND [ FRAME frame ] field ] ... ]
[ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
[ NO-PREFETCH ]
|
The name of a database table, a temp-table that you named in a DEFINE TEMP-TABLE statement, or a buffer that you named in a DEFINE BUFFER statement. If
record is the name of a table,
record actually refers to the default buffer of the specified database table or temp-table. Also, the default buffer for a database table is always scoped to the main external procedure or class definition block even if you reference the default buffer in a sub-block, such as an internal procedure or method of a class.
|
Note:
|
Field lists should be used with caution because they can cause unexpected runtime errors. It is possible, for example, that you may have eliminated a field that is required by a new or revised subroutine or trigger. The result can be a runtime error that may be difficult to debug. You can use the -rereadfields startup option if you have unexpected runtime errors resulting from field lists. When an error occurs, the -rereadfields startup option causes the AVM to ignore the field list and to fetch the entire record. Also note the difference between -rereadfields and -fldisable. The Field List Disable ( -fldisable) option causes the AVM to ignore all field lists. The Reread Field List ( -rereadfields) option causes the AVM to ignore only those field lists that raise an error due to a missing field. Therefore, using -rereadfields is likely to have less of a negative impact on performance.
|
{ FIELDS [ ( [ field ... ] ) ]
| EXCEPT [ ( [ field ... ] ) ]
}
|
The FIELDS option specifies the fields you want to include in a record retrieval, and the EXCEPT option specifies the fields that you want to exclude from a record retrieval. The
field parameter is the name of a single field in the specified table. If
field is an array reference, the whole array is retrieved even if only one element is specified. Specifying FIELDS with no
field references causes the AVM to retrieve sufficient information to extract the ROWID value for a specified record (returnable using the ROWID function). Specifying EXCEPT with no
field references or specifying
record without a
field-list causes the AVM to retrieve a complete record.
|
Note:
|
Always specify fields that you plan to reference in the field list. Only those extra fields that the client requires for record selection are added to the specified field list. The AVM distributes record selection between the client and server depending on a number of factors that change with each OpenEdge release. Therefore, never rely on fields that you did not specify but which the AVM fetches for its own needs; they might not always be available. There is no additional cost to specify a field in the list that you otherwise expect the AVM to provide.
|
This statement retrieves the Customer.CustNum field in addition to those specified in the field lists because it is required to satisfy the inner join between the
Customer and
Order tables:
The CustNum field is the only component of the primary index of the Customer table.
If you use the constant option, you can use it only once in a single Record phrase, and it must precede any other options in the Record phrase.
Specifies a left outer join between record and the table (or join) specified by the previous Record phrase(s) of an OPEN QUERY statement. A left outer join combines and returns data from the specified tables in two ways. First, the records selected for the table (or join) on the left side combine with each record selected using the OF or WHERE options from the table on the right (
record). Second, the records selected for the table (or join) on the left side combine with the Unknown value (
?) for the fields from the table on the right (
record) for which no records are selected using the OF or WHERE options. The join is ordered according to the given sort criteria starting with the left-most table in the query.
|
Note:
|
If you specify the OUTER-JOIN option, you must also specify the OUTER-JOIN option in all succeeding Record phrases of the query to obtain a left outer join. That is, for multiple Record phrases, all joins in the query following your first left outer join must also be left outer joins. Otherwise, the result is an inner join for all records up to the last inner join in the query. For more information, see OpenEdge Getting Started: ABL Essentials.
|
The OUTER-JOIN option is supported only in the OPEN QUERY statement and in Record phrases specified after the first Record phrase in the OPEN QUERY statement. The LEFT keyword is optional with OUTER-JOIN. If you specify OUTER-JOIN, you must also specify the OF option, WHERE option, or any combination of the OF and WHERE options. These options are required to select
record (the right-most table) for the specified left outer join. For example:
This query specifies a left outer join between Customer and Order, and also between that join and OrderLine. Thus, for each Customer record that has no Orders or has no Orders with an
OrderNum less than 50, the query returns the Customer fields and ? for all fields of the Order and OrderLine tables. In addition, if there are no OrderLine records with
ItemNum less than 15 for any selected Customer and Order, the query returns ? for all fields of OrderLine. Otherwise, it returns each Customer record along with its first selected Order record and OrderLine record.
In all statements where multiple Record phrases are allowed (including DO, FOR, OPEN QUERY, and REPEAT statements), the default join (without the OUTER-JOIN option) is an inner join between
record and the table (or join) specified by the previous Record phrase(s). An inner join returns the records selected for the table (or join) on the left side combined with each selected record from the table on the right (
record). For an inner join, no records are returned for the table (or join) on the left for which no record is selected from the table on the right (
record).
|
Note:
|
If you specify a Record phrase as an inner join, the current Record phrase and all preceding Record phrases in the query participate in contiguous inner joins, even if prior Record phrases specify the OUTER-JOIN option. Thus, for multiple Record phrases, all joins in the query up to the right-most inner join result in contiguous inner joins. For more information, see OpenEdge Getting Started: ABL Essentials.
|
Relates record to one other table specified by a table or buffer name (
table). The relationship is based on common field names between
record and
table that also participate in a UNIQUE index for either
record or
table. When you use OF and the UNIQUE index is multi-field, all fields in the index participate in the match criteria. A reference to
table must appear in a prior joined Record phrase in the same statement, or remain in scope from a prior record reading statement, such as a FIND statement.
Qualifies the records you want to access. The expression is a constant, field name, variable name, or expression whose value you want to use to select records. You can use the WHERE keyword even if you do not supply an
expression. For example:
|
Note:
|
You cannot reference an OpenEdge BLOB or CLOB field in a WHERE clause. However, in OpenEdge DataServer for MS SQL Server, a non-legacy BLOB or CLOB server data type can be mapped to the OpenEdge CHARACTER data type in a WHERE clause operand as long as the total size of the resolved WHERE clause (which might include the CHARACTER-mapped value) does not exceed 30000 bytes.
|
|
Note:
|
Use the - noroutineinwhere parameter to force the compiler to raise an error, if the WHERE expression contains a user-defined function, a class method, or a class property, which implements the GET method using ABL statements.
|
field CONTAINS search-expression
|
In this syntax, field represents a field in which a word index has been defined. The
search-expression specifies one or more words to search for. It must evaluate to a string with this syntax:
"word [ [ & | | | ! | ^ ] word ] ..."
|
Each word is a word to search for. The ampersand (
&) represents a logical AND; the vertical line (
|), exclamation point (
!), or caret (
^) represent a logical OR. You can use an asterisk (
*) as a wildcard, but only at the end of a
word. Using an asterisk anywhere else in a
word raises an error.
The SKIP-GROUP-DUPLICATES option allows you to skip duplicate data for a tenant group.
If a query has been coded with a TENANT-WHERE option, and compiled, but is run by a regular tenant, the AVM raises a run-time error.
FOR EACH record ... [ USE-INDEX index | TABLE-SCAN ]
|
Here mytable is in a Type II Storage area. The AVM uses TABLE-SCAN instead of WHOLE-INDEX to access the record blocks. The AVM accesses both primary index and record blocks, if the WHOLE-INDEX keyword is used.
USING [ FRAME
frame ] field [ AND
[ FRAME
frame ] field ] . . .
The CustNum field is a non-abbreviated index. However, if the
Name field is an abbreviated index of the Customer table, the AVM converts the FIND statement with the USING option. For example:
Tells the AVM to put an EXCLUSIVE-LOCK on records as they are read. Other users cannot read or update a record that is EXCLUSIVE-LOCKed, except by using the NO-LOCK option. They can access that record only when the EXCLUSIVE-LOCK is released. The AVM automatically puts a SHARE-LOCK on a record when it is read and automatically puts an EXCLUSIVE-LOCK on a record when it is updated.
If a procedure finds a record and it places it in a buffer using NO-LOCK and you then refind that record using NO-LOCK, the AVM does not reread the record. Instead, it uses the copy of the record that is already stored in the buffer. To make sure the AVM gets a fresh copy of the record from the database, you must release all NO-LOCK buffers referencing the record or retrieve the record with EXCLUSIVE-LOCK. Note that you can use the Reread Nolock (-
rereadnolock) startup parameter to change this default behavior.
When you read records with NO-LOCK, you have no guarantee of the overall consistency of those records because another user might be in the process of changing them. When values are assigned to indexed fields for a newly created record or are modified in an existing record, the index is immediately updated to reflect the change. However the copy of the data record in the buffers used by the database server might not be updated until later in the transaction. For example, the following procedure might display a
CustNum of 0 if another user’s active transaction has created a record and assigned a value to the indexed field
CustNum that is greater than 100:
In the r-recph.p procedure, there are two Record phrases that make an inner join between the Customer and Order tables.
FOR EACH Customer WHERE Customer.CreditLimit GE 50000,
EACH Order OF Customer:
DISPLAY Customer.CustNum Customer.Name Customer.CreditLimit Order.Orderum
Order.OrderDate Order.Terms.
END.
|
REPEAT:
FIND NEXT Customer USE-INDEX CountryPost WHERE Customer.Name BEGINS "S"
EXCLUSIVE-LOCK.
UPDATE Customer.Name Customer.Country Customer.PostalCode Customer.Phone.
END.
|
In the r-recph2.p procedure, there is one Record phrase:
Using the zip index named country-post rather than the CustNum index (the primary index for the Customer table), the FIND statement reads only those Customer records that have a
Name that begins with an s. The FIND also places an EXCLUSIVE-LOCK on each record as it is read. This lock is released at the end of the REPEAT block.
|
|
Specifying a field list (field-list) for record can increase the performance of remote (network) record retrieval substantially over specifying record alone.
|
|
|
If you reference an unfetched database field at run time, the AVM raises the ERROR condition. ABL does not perform a compile-time check to ensure that the field is fetched because the compiler cannot reliably determine how a particular record will be read (that is, whether it is retrieved using a FIND statement, retrieved with or without a field list, including additional fields to satisfy join conditions, etc.).
|
|
|
You can specify the Field List Disable (-fldisable) or the Reread Field List ( -rereadfields) startup parameters to cancel field list retrieval and force the AVM to retrieve complete records. These are run-time client session parameters that are especially useful for deployed applications whose database triggers are later redefined to reference unfetched fields (raising the ERROR condition).
|
Note the difference between -rereadfields and
-fldisable. The Field List Disable (
-fldisable) option causes the AVM to ignore
all field lists and to fetch the entire record for every query. The Reread Field List (
-rereadfields) option causes the AVM to ignore a particular field list and fetch the entire record only when an error occurs due to a missing field. Therefore, using
-rereadfields is likely to have less of a negative impact on performance.
