Release 10.1A: OpenEdge Development:
Progress 4GL Reference

> ## 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 ]
>     [ USE-INDEX index ]
>     [ USING [ FRAME frame ] field
>         [ AND [ FRAME frame ] field ] ... ]
>     [ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
>     [ NO-PREFETCH ]

> Note: You can specify the OUTER-JOIN, OF, WHERE, USE-INDEX, and USING options in any order. You cannot use `field-list` in an OPEN QUERY statement. You cannot use OUTER-JOIN or EXCLUSIVE-LOCK in a CAN-FIND function.

> ``record``

> The name of a table or buffer that you named in a DEFINE BUFFER statement.

> 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 using a FOR,
> DO PRESELECT, or REPEAT PRESELECT statement. Field lists are also available for queries using the DEFINE QUERY statement. 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 Progress 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 Progress 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 name balance.

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

> - 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 , OpenEdge Data Management: DataServer for ODBC , 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. Progress distributes record selection between the client and server depending on a number of factors that change with each Progress release. Therefore, never rely on fields that you did not specify but which Progress 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 Progress to provide.

> This statement retrieves the customer.cust-num 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),
>       EACH order FIELDS (order-num sales-rep) OF customer:
>         DISPLAY customer.name customer.cust-num
>                 order.order-num order.sales-rep.

> However, do not rely on Progress to always provide such extra fields. For reliability, add the cust-num field to the customer field list as follows:

>     FOR EACH customer FIELDS (name cust-num),
>       EACH order FIELDS (order-num sales-rep) OF customer:
>         DISPLAY customer.name customer.cust-num
>                 order.order-num order.sales-rep.

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

> Progress converts this FIND statement with the `constant` option of 1 to the following statement:

>     FIND customer WHERE cust-num = 1.

> The cust-num 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 Development: Progress 4GL Handbook .

> 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.order-num < 50
>       FIRST order-line OUTER-JOIN OF order WHERE order-line.item-num < 15.

> This query specifies a left outer join between customer and order, and also between that join and order-line. Thus, for each customer record that has no orders or has no orders with an order-num less than 50, the query returns the customer fields and ? for all fields of the order and order-line tables. In addition, if there are no order-line records with item-num less than 15 for any selected customer and order, the query returns ? for all fields of order-line. Otherwise, it returns each customer record along with its first selected order record and order-line 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 order-line. Thus, this query only returns customer records that have at least one order with order-num less than 50 that also have at least one order-line with item-num less than 15, and it returns just the first such order and order-line for each customer record.

>     OPEN QUERY q1 PRESELECT EACH customer,
>       FIRST order OUTER-JOIN OF customer WHERE order.order-num < 50
>       FIRST order-line OF order WHERE order-line.item-num < 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 Development: Progress 4GL Handbook .

> For more information on joins in the 4GL, see OpenEdge Development: Progress 4GL Handbook .

> `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 Progress selects the customer record related to the order record currently in use. Progress converts the FIND statement with the OF option to a FIND statement with the WHERE option.

>     PROMPT-FOR order.order-num.
>     FIND order USING order-num.
>     DISPLAY order.
>     FIND customer OF order.
>     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 WHERE customer.cust-num =
>       order.cust-num.

> `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 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 ODBC or OpenEdge Data Management: DataServer for ORACLE , for additional information on how this feature will perform.

> Note: You cannot reference a BLOB or CLOB field in a WHERE clause.

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

> Here is an example using the CONTAINS clause:

>     FOR EACH item WHERE cat-description CONTAINS "ski":
>       DISPLAY item-name cat-description VIEW-AS EDITOR SIZE 60 BY 15.
>     END.

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

> `USE-INDEX `index``

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

> `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.cust-num.
>     FIND customer USING cust-num.

> This FIND statement is the same as this statement:

>     FIND customer WHERE customer.cust-num =
>       INPUT customer.cust-num.

> The cust-num field is a non-abbreviated index. However, if the name field is an abbreviated index of the customer table, Progress converts the FIND statement with the USING option. For example:

>     PROMPT-FOR customer.name.
>     FIND customer USING name.

> The following statement is a result of the previous one:

>     FIND customer WHERE customer.name
>       BEGINS INPUT name.

> `SHARE-LOCK`

> Tells Progress 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, Progress 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 Progress tries to read a record that is EXCLUSIVE-LOCKed by another user, Progress waits to read the record until the EXCLUSIVE-LOCK is released. Progress 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, Progress disregards the SHARE-LOCK option.

> `EXCLUSIVE-LOCK`

> Tells Progress 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. Progress 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, Progress 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, Progress disregards the EXCLUSIVE-LOCK option. Also, CAN-FIND cannot use the EXCLUSIVE-LOCK option.

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

> `NO-LOCK`

> Tells Progress 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, Progress 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.
>     rid = ROWID(customer).
>     FIND customer WHERE
>       ROWID(customer) = rid EXCLUSIVE-LOCK.

> 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, Progress does not reread the record. Instead, it uses the copy of the record that is already stored in the buffer.

> 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 cust-num of 0 if another user’s active transaction has created a record and assigned a value to the indexed field cust-num that is greater than 100:

>     FOR EACH customer WHERE cust-num > 100 NO-LOCK:
>       DISPLAY cust-num.
>     END.

> If you are using a record from a work table, Progress 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, Progress 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 FIELDS (cust-num name credit-limit)
>             WHERE credit-limit GE 50000,
>         EACH order FIELDS (order-num order-date terms) OF customer:
>         DISPLAY customer.cust-num customer.name credit-limit
>           order.order-num order-date order.terms.
>     END.

> Using these Record phrases, the FOR EACH block reads a customer record only if it has a credit-limit value greater than 50000 and at least one order record associated with it.

> r-recph2.p
>     REPEAT:
>         FIND NEXT customer USE-INDEX country-post WHERE name BEGINS "S"
>             EXCLUSIVE-LOCK.
>         UPDATE name country postal-code phone.
>     END.

> In the`r-recph2.p` procedure, there is one Record phrase:

>     customer USE-INDEX country-post WHERE name BEGINS "S" EXCLUSIVE-LOCK"

> Using the zip index named country-post rather than the cust-num 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: Progress 4GL Handbook .

> - If you reference an unfetched database field at run time, Progress raises the ERROR condition. Progress 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, Progress reads the whole record, again, to complete the delete or update.

> - You can specify the Field List Disable (`-fldisable`) startup parameter to cancel field list retrieval and force Progress to retrieve complete records. This is a runtime client session parameter that is especially useful for deployed applications whose database triggers are later redefined to reference unfetched fields (raising the ERROR condition). Using -fldisable provides a workaround that allows the application to run (although more slowly) until the application can be fixed.

> - You cannot specify field lists or joins in a FIND statement, or specify field lists in an OPEN QUERY statement.

> - You cannot use the CONTAINS operator with a temporary table.

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

> - Temporary 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. Progress 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.

> - SpeedScript – The only invalid option is USING FRAME.

> #### See also

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

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095