Progress
Language 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:
SYNTAX
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 of a 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.
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. This is the syntax for field-list.
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.
This statement retrieves all fields of the customer table except the name and balance fields.
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 the Progress Database Administration Guide and Reference.
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.- Retrieves a complete record for DataServers that do not support SHARE-LOCK. For more information, see the appropriate DataServer guides, Progress DataServer for ODBC Guide and Progress DataServer for ORACLE Guide.
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.
However, do not rely on Progress to always provide such extra fields. For reliability, add the cust-num field to the customer field list.
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.
Progress converts this FIND statement with the constant option of 1 to the following statement:
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 unknown values (?) 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 the Progress Programming 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.
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,
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 the Progress Programming Handbook .
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).
This 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.
For more information on joins in the 4GL, see the Progress Programming 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.
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.
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.
NOTE: The WHERE clause may not work the same way against a DataServer as it does against the Progress database. Please refer to the appropriate DataServer Guide, Progress DataServer for ODBC Guide or Progress DataServer for ORACLE Guide, for additional information on how this feature will perform.
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.
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.
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.
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.
You cannot word index a field that contains double-byte data and the field and search-expression arguments of the CONTAINS option cannot contain double-byte characters.
For more information on this option, see the NOTES section of this reference entry.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.
This FIND statement is the same as this statement.
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.
The following statement is a result of the previous one.
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.
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.
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.
EXAMPLESIn the
r-recph.p
procedure, there are two Record phrases that make an inner join between the customer and order tables.
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.
In the
r-recph2.p
procedure, there is one Record phrase.
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 the Progress Programming 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 run-time 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.
- 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 © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |