Progress
Language Reference
OPEN QUERY Statement
Opens a query, which might have been previously defined in a DEFINE QUERY statement. Opening a query makes it available for use within a GET statement, or in a browse widget.
SYNTAX
query
The query to open. The query name may have been defined previously in a DEFINE QUERY statement. Otherwise, the OPEN QUERY statement implicitly defines the query.
{ FOR | PRESELECT } EACH record-phrase
Specifies the first buffer of the query. The following is the syntax for record-phrase.
If the query was previously defined, the buffers referenced by the record-phrase must be the same buffers referenced in the DEFINE QUERY statement and in the same order. For more information, see the Record Phrase reference entry.
Note that the first buffer must be qualified with EACH rather than the FIRST option. That is, the OPEN QUERY statement implies the possibility of a multi-row result, whether or not only one row is returned.
If you specify PRESELECT rather than FOR, then Progress preselects the records for the query. During the preselect process, Progress applies whatever locking is specified in the OPEN QUERY statement or, if none is specified, SHARE-LOCK. It then reads the ROWID for each record into the result list. (If you do not specify PRESELECT, Progress might pass through the records anyway to presort them. In this case, Progress applies NO-LOCK to each record during this pass.)
{ EACH | FIRST | LAST } record-phrase
Specifies subsequent buffers in the query. Each subsequent buffer specifies a join with the previous buffer(s) according to the record-phrase. If the query was previously defined, the buffers referenced by the record-phrase must be the same buffers referenced in the DEFINE QUERY statement and in the same order. For more information on specifying joins in Record phrases, see the Record Phrase reference entry.
query-tuning-phrase
Allows programmatic control over the execution of a DataServer query.
For more information, see your Progress DataServer Guides , Progress DataServer for ODBC Guide and Progress DataServer for ORACLE Guide.
BY expression [ DESCENDING ]
Specifies the order in which records are to be returned. If an index is defined with the right leading keys to satisfy the BY clause, Progress uses that index to sort the records. Otherwise, Progress must presort the records before the first fetch when you specify BY.
string
A CHARACTER expression that evaluates to the string whose collation value you want to compute.
strength
A CHARACTER expression that evaluates to one of the following:
- RAW — Causes COLLATE to compute the collation value of the string from its binary value.
- CASE-SENSITIVE — Causes COLLATE to compute the case-sensitive collation value of the string using a particular collation table.
- CASE-INSENSITIVE — Causes COLLATE to compute the case-insensitive collation value of the string using a particular collation table.
collation
A CHARACTER expression that evaluates to the name of a collation table.
If collation does not appear, COLLATE uses the collation table of the client.
Progress reports an error and stops execution if one of the following occurs:
INDEXED-REPOSITION
If you specify this option, Progress attempts to optimize subsequent REPOSITION TO ROWID operations on the query. This can improve the performance of REPOSITION operations that must jump over many records in a simple query. Optimization is not possible if the database is not a Progress database, or sorting or preselection is performed. In these cases, the INDEXED-REPOSITION option is ignored and no error is reported.
The optimization has some side effects. When you perform a REPOSITION TO ROWID with this optimization, Progress discards the original result list and begins a new one. Therefore, scrolling forward or backward in the list might return different records from before. Also, the values of the NUM-RESULTS and CURRENT-RESULT-ROW become invalid. If the query has an associated browse, any selections in that browse are also lost. Lastly, the vertical scrollbar thumb is disabled. Because of these side-effects, use this option selectively.
MAX-ROWS num-results
Specifies the maximum number of records to be returned by the query. Any other records satisfying the query are ignored and no error is raised. The limit is imposed before any sorting occurs; Progress retrieves records up to the number specified and then sorts those records.
This option is valid for scrolling queries only. You can use it to prevent a long delay that might occur if a query returns many more records than you expect.
EXAMPLEThe following example opens a query on the customer, order, order-line, and item table.
Note the use of field lists in the DEFINE QUERY statement. This can improve the performance of remote database queries significantly.
NOTES
- If the query you reference in an OPEN QUERY statement is already open, then that query is closed and a new query is opened.
- If you use the USE-INDEX option of the Record phrase, Progress uses only that index. Records are returned in index order.
- The locking options of the OPEN QUERY statement define the default locking for records fetched by the query. You can override the default by using a locking option in the GET statement. Note, however, that in the OPEN QUERY statement you can specify a separate lock type for each buffer; in the GET statement you can specify only one lock type that applies to all buffers in a join.
- The record locking behavior specified for a query in the DEFINE BROWSE statement overrides the record locking behavior specified with the OPEN QUERY statement. The default record locking behavior of a browse widget is NO-LOCK. The default record locking behavior of a query defined with the OPEN QUERY statement is SHARE-LOCK. If you define a query and a browse widget for the query without explicitly defining record locking behavior, the query will have the NO-LOCK behavior.
- Each time you open a query associated with a browse widget, the data in the browse is refreshed.
- You cannot use the CAN-FIND function in a WHERE clause. Doing so generates a compiler error.
- If you open a query that has already been defined with multiple buffers, you must specify the buffers in the same order in the OPEN QUERY as they were specified in the DEFINE QUERY statement.
- Once the query has been opened, you cannot change the buffers that it references, even if the query is closed and re-opened. For example, a buffer, buff1, is created for the customer table in a DEFINE QUERY or OPEN QUERY for the query, qry1. The query is run and closed. You cannot now DEFINE or OPEN qry1 with buff1 for the item table. You can reuse buffers with CREATE QUERY, but you must re-run QUERY-PREPARE.
- The COLLATE option computes the collation value of a string after applying a particular “strength” (RAW, CASE-SENSITIVE, or CASE-INSENSITIVE) and, optionally, a particular collation table.
SEE ALSO
CLOSE QUERY Statement, CREATE QUERY Statement, CURRENT-RESULT-ROW Function, DEFINE BROWSE Statement, DEFINE QUERY Statement, GET Statement, NUM-RESULTS Function, QUERY-OFF-END Function, QUERY-PREPARE( ) Method, REPOSITION Statement
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |