Record phrase

Identifies the record or records you want to verify using the CAN-FIND function, retrieve with a FIND statement, query with a FOR statement or OPEN QUERY statement, or preselect in a DO or REPEAT block.

The Record phrase syntax describes three kinds of information:

• Qualifies the record(s) to access in the table
• Specifies the index to use when locating records
• Defines the type of record lock to apply when the records are read

Syntax

{ 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 ]

record

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.

To access a record in a table defined for multiple databases, you must qualify the record's table name with the database name. Use this syntax to refer to a record in a table for a specific database:

dbname.tablename

You do not have to qualify the reference if record is the name of a defined buffer.

field-list

Specifies a list of fields to include or exclude when you retrieve records.

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.

Field lists can be added to a FOR, DO PRESELECT, or REPEAT PRESELECT statement. Field lists are also available for queries using the DEFINE QUERY statement, except when the query is against a temp-table. The following is the syntax for field-list:

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

This statement retrieves only the Name and Balance fields of the Customer table:

FOR EACH customer FIELDS (name balance): DISPLAY name balance.

This statement retrieves all fields of the Customer table except the Name and Balance fields:

FOR EACH Customer EXCEPT (Name Balance):   DISPLAY Customer EXCEPT Customer.Name Customer.Balance.

When you specify a field list, the AVM might retrieve additional fields or the complete record depending on the type of retrieval operation and the DataServer that provides the record. Thus, the AVM:

• Retrieves any additional fields required by the client to complete the record selection.
• Retrieves a complete record when the record is fetched with EXCLUSIVE-LOCK. This ensures proper operation of updates and the local before-image (BI) file. For information on the local BI file, see OpenEdge Data Management: Database Administration.
• Retrieves a complete record for DataServers that do not support SHARE-LOCK. For more information, see the appropriate DataServer guides (OpenEdge Data Management: DataServer for Microsoft SQL Server and OpenEdge Data Management: DataServer for Oracle).

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:

FOR EACH Customer FIELDS(Name) NO-LOCK, EACH Order FIELDS(OrderNum SalesRep) OF Customer NO-LOCK:  DISPLAY Customer.Name Customer.CustNum Order.OrderNum Order.SalesRep.

However, do not rely on the AVM to always provide such extra fields. For reliability, add the CustNum field to the Customer field list as follows:

FOR EACH Customer FIELDS(Name CustNum) NO-LOCK, EACH Order FIELDS(OrderNum SalesRep) OF Customer NO-LOCK:  DISPLAY Customer.Name Customer.CustNum Order.OrderNum Order.SalesRep.

constant

The value of a single component, unique, primary index for the record you want. This option is not supported for the OPEN QUERY statement:

FIND Customer 1.

The AVM converts this FIND statement with the constant option of 1 to the following statement:

FIND Customer NO-LOCK WHERE Customer.CustNum = 1.

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.

[ LEFT ] OUTER-JOIN

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:

OPEN QUERY q1 PRESELECT EACH Customer, FIRST Order OUTER-JOIN OF Customer WHERE Order.OrderNum < 50 FIRST OrderLine OUTER-JOIN OF Order WHERE OrderLine.ItemNum < 15.

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

The following query specifies an inner join between Customer and Order, and also between that join and OrderLine. Thus, this query only returns Customer records that have at least one Order with OrderNum less than 50 that also have at least one OrderLine with ItemNum less than 15, and it returns just the first such Order and OrderLine for each Customer record.

OPEN QUERY q1 PRESELECT EACH Customer, FIRST Order OUTER-JOIN OF Customer WHERE Order.OrderNum < 50 FIRST OrderLine OF Order WHERE OrderLine.ItemNum < 15.

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.

For more information on joins in ABL, see OpenEdge Getting Started: ABL Essentials.

OF table

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.

Note: For the OF keyword to properly detect a relationship between two tables, only one such relationship is allowed.

In this example, the OF option relates the order table to the Customer table; thus the AVM selects the Customer record related to the Order record currently in use. The AVM converts the FIND statement with the OF option to a FIND statement with the WHERE option.

PROMPT-FOR Order.OrderNum. FIND Order NO-LOCK USING Order.OrderNum. DISPLAY Order. FIND Customer OF Order NO-LOCK. DISPLAY customer.

You can use WHERE to access related tables, whether or not the field names of the field or fields that relate the tables have the same name. For example:

FIND Customer NO-LOCK WHERE Customer.CustNum = Order.CustNum.

WHERE expression

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:

FOR EACH Customer NO-LOCK WHERE {*}

The WHERE clause may not work the same way against a DataServer as it does against the OpenEdge database. Refer to the appropriate DataServer Guide (OpenEdge Data Management: DataServer for Microsoft SQL Server or OpenEdge Data Management: DataServer for Oracle), for additional information on how this feature will perform.

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.

In an OPEN QUERY statement or FOR statement, the WHERE clause can use the CONTAINS operator to reference a field with a word index. This is the syntax for the CONTAINS operator:

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.

You can use parenthesis to establish precedence in the search string. For example, the following expression means to search for

Here is an example using the CONTAINS clause:

FOR EACH Item NO-LOCK WHERE Item.CatDescription CONTAINS "ski": DISPLAY Item.ItemName Item.CatDescription VIEW-AS EDITOR SIZE 60 BY 15. END.

You can use parenthesis to establish presentness in the search string. For example, the following expression returns a subset of the catalog descriptions containing "ski":

WHERE Item.CatDescription CONTAINS "ski & (gog* ! pol*)"

Note: The CONTAINS option is not allowed in a FIND statement. If the session is started with the Version 6 Query (-v6q) parameter, the CONTAINS option is also not allowed in a FOR statement.

Note: For information about compiling, storing, and applying the UTF-8 word-break rules to a database, see OpenEdge Development: Internationalizing Applications.

TENANT-WHERE expression [ SKIP-GROUP-DUPLICATES ]

Allows a super tenant to specify which tenants are to be included in a query, where expression is a relational operation, or a logical combination of relational operations, that test for a character or integer expressions that correspond to a tenant name or tenant ID, respectively. In order to use the TENANT-WHERE functionality at run time, you must be a super tenant, not a regular tenant.

You can only use the TENANT-WHERE option in the FOR EACH statement or option of a query. If you use a TENANT-WHERE in a join, you can have only one such option at one level of the join.

Note: This option does not change the effective tenancy for a super tenant. For more information on effective tenancy, see the entry for the SET-EFFECTIVE-TENANT function.

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.

Note that any use of the TENANT-ID or TENANT-NAME functions in a TENANT-WHERE expression must not specify the optional database parameter, as the compiler assumes the database is the same as for the query in which the TENANT-WHERE option is specified.

For more information on this option, and examples, see the sections on multi-tenant ABL in OpenEdge Development: Programming Interfaces.

USE-INDEX index

Identifies the index you want to use while selecting records. If you do not use this option, the AVM selects an index to use based on the criteria specified with the WHERE, USING, OF, or constant options.

TABLE-SCAN

You can use the TABLE-SCAN option as an alternative to the USE-INDEX keyword in FOR EACH statements.

TABLE-SCAN provides the most efficient access method when retrieving all rows of a temp-table or a database table in a Type II Storage area. TABLE-SCAN returns these rows without using an index. When the TABLE-SCAN keyword is used, the AVM only accesses the record block instead of both record and index blocks.

FOR EACH record ...[ USE-INDEX index | TABLE-SCAN ]

For a table in a Type I storage, the AVM uses the default index to perform the scan instead of TABLE-SCAN. If the QryInfo log entry is set, the AVM logs message if the TABLE-SCAN keyword is used on a Type I Storage area.

Here is an example using the TABLE-SCAN keyword:

FOR EACH mytable TABLE-SCAN: totalCost = totalCost + mytable.cost END.

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.

For more information, see also Web Paper: ABL Database Triggers and Indexes.

USING [ FRAME frame ] field [ AND [ FRAME frame ] field] . . .

One or more names of fields for selecting records. You must have previously entered each field you name in this option, usually with a PROMPT-FOR statement. The field must be viewed as a fill-in or text widget.

The USING option translates into an equivalent WHERE option:

PROMPT-FOR Customer.CustNum. FIND Customer NO-LOCK USING Customer.CustNum.

This FIND statement is the same as this statement:

FIND Customer NO-LOCK WHERE Customer.CustNum = INPUT Customer.CustNum.

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:

PROMPT-FOR Customer.Name. FIND Customer NO-LOCK USING Customer.Name.

The following statement is a result of the previous one:

FIND Customer NO-LOCK WHERE Customer.Name BEGINS INPUT Customer.Name.

SHARE-LOCK

Tells the AVM to put a SHARE-LOCK on records as they are read. Another user can read a record that is share locked, but cannot update it. By default, the AVM puts a SHARE-LOCK on a record when it is read (unless it uses a CAN-FIND function), and automatically puts an EXCLUSIVE-LOCK on a record when it is modified (unless the record is already EXCLUSIVE-LOCKed).

In a CAN-FIND function, NO-LOCK is the default. Also, CAN-FIND cannot use EXCLUSIVE-LOCK.

If you use the SHARE-LOCK option and the AVM tries to read a record that is EXCLUSIVE-LOCKed by another user, the AVM waits to read the record until the EXCLUSIVE-LOCK is released. The AVM displays a message to the user of that procedure, identifying the table that is in use, the user ID of the user, and the tty of the terminal using the table.

If you are using a record from a work table, the AVM disregards the SHARE-LOCK option.

EXCLUSIVE-LOCK

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 record is read specifying EXCLUSIVE-LOCK, or if a lock is automatically changed to EXCLUSIVE-LOCK by an update, user's read or update will wait if any other user SHARE-LOCKed or EXCLUSIVE-LOCKed the record.

When a procedure tries to use a record that is EXCLUSIVE-LOCKed by another user, the AVM displays a message identifying the table that is in use, the user ID of the user, and the tty of the terminal using the table.

If you are using a record from a work table, the AVM disregards the EXCLUSIVE-LOCK option. Also, CAN-FIND cannot use the EXCLUSIVE-LOCK option.

Specifying EXCLUSIVE-LOCK causes the AVM to retrieve complete records, even when the record is specified with field-list.

NO-LOCK

Tells the AVM to put no locks on records as they are read, and to read a record even if another user has it EXCLUSIVE-LOCKed.

Another user can read and update a record that is not locked. By default, the AVM puts a SHARE-LOCK on a record when it is read (unless it uses a CAN-FIND function, which defaults to NO-LOCK), and automatically puts an EXCLUSIVE-LOCK on a record when it is updated (unless the record is already EXCLUSIVE-LOCKed). A record that has been read NO-LOCK must be reread before it can be updated.

DEFINE VARIABLE rid AS ROWID NO-UNDO. rid = ROWID(customer). FIND Customer EXCLUSIVE-LOCK WHERE ROWID(Customer) = rid.

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:

FOR EACH Customer NO-LOCK WHERE Customer.CustNum > 100 DISPLAY Customer.CustNum. END.

If you are using a record from a work table, the AVM disregards the NO-LOCK option.

NO-PREFETCH

Specifies that only one record is sent across the network at a time. If you specify field-list, only the specified fields and any additional fields required for record selection are sent. If you do not specify this option, the AVM can send more than one record from the server to the client in each network packet.

Examples

In the r-recph.p procedure, there are two Record phrases that make an inner join between the Customer and Order tables.

r-recph.p

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.

Using these Record phrases, the FOR EACH block reads a Customer record only if it has a CreditLimit value greater than 50000 and at least one Order record associated with it.

r-recph2.p

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:

Customer USE-INDEX CountryPost WHERE Customer.Name BEGINS "S" EXCLUSIVE-LOCK

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.

In the output of this procedure, all the Customer names begin with s and the customers are displayed in order by country and then postal code.

Notes

• Specifying a field list (field-list) for record can increase the performance of remote (network) record retrieval substantially over specifying record alone. For more information, see OpenEdge Development: ABL Handbook.
• 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.).
• Do not use a field list if you delete or update the record shortly after the record retrieval. Otherwise, the AVM reads the whole record, again, to complete the delete or update.
• 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.

• You cannot specify field lists or joins in a FIND statement, or specify field lists in an OPEN QUERY statement.
• If used, the CONTAINS operator must appear in the outer-most WHERE expression. You can combine it with other expressions at the outer level using the AND and OR operators. However, you cannot apply the NOT operator to a CONTAINS expression.
• You cannot reference a BLOB or CLOB field in a WHERE clause.
• Temp-tables and work tables can be used in join conditions specified with the OF option as long as the OF option requirements identified earlier in this section have been satisfied.
• Do not compare case-sensitive data with case-insensitive data in a WHERE expression. The AVM both cannot determine the results and does not raise the ERROR condition if you specify data with mixed case sensitivity in selection criteria because:
  • Mixed case sensitivity in selection criteria is handled differently by different DataServers.
  • Mixed case-sensitivity results for the same DataServer can be different depending on whether the query is resolved on the client or the server.
  • Some national languages do not support the concept of case sensitivity.

  Thus, such queries cannot be reliably resolved in any way.

• For SpeedScript, the only invalid option is USING FRAME.
• In a class definition, ABL treats the default buffer of a database table that you reference as an instance data member of the class. Therefore, you cannot access a default database buffer from a static member of a class, such as a static query or method; instead, you can define an alternate static buffer data member to access records of the database table from another static class member.
• You can reference any of the following functions and attributes in the expression of a WHERE or TENANT-WHERE option as long as the buffer specified for the function or attribute is not the same as the buffer of the query or FOR EACH statement:
  • BUFFER-GROUP-ID attribute
  • BUFFER-GROUP-ID function
  • BUFFER-GROUP-NAME attribute
  • BUFFER-GROUP-NAME function
  • BUFFER-TENANT-ID attribute
  • BUFFER-TENANT-ID function
  • BUFFER-TENANT-NAME attribute
  • BUFFER-TENANT-NAME function

See also

DEFINE QUERY statement, DO statement, FIND statement, FOR statement, OPEN QUERY statement, REPEAT statement