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
OPEN QUERY query { FOR | PRESELECT } EACH record-phrase
[ , { EACH | FIRST | LAST } record-phrase]...
[ query-tuning-phrase ]
[ BREAK ]
[ BY expression [ DESCENDING ]
| COLLATE ( string , strength [ , collation ] ) [ DESCENDING ]
] ...
[ INDEXED-REPOSITION ]
[ MAX-ROWS num-results]
|
-
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:
record
[ [ LEFT ] ] [ OF table ]
[ WHERE expression ]
[ USING [ FRAME frame ] field
[ AND [ FRAME frame ] field ] ... ]
[ USE-INDEX index ]
[ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
[ NO-PREFETCH ]
|
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 the AVM preselects the
records for the query. During the preselect process, the AVM 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, the AVM might pass through
the records anyway to presort them. In this case, the AVM 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.
Following is the syntax for the query-tuning-phrase:
QUERY-TUNING
(
[ LOOKAHEAD [ CACHE-SIZE integer]| NO-LOOKAHEAD ]
[ DEBUG { SQL | EXTENDED }| NO-DEBUG ]
[ SEPARATE-CONNECTION | NO-SEPARATE-CONNECTION ]
[ JOIN-BY-SQLDB | NO-JOIN-BY-SQLDB ]
[ BIND-WHERE | NO-BIND-WHERE ]
[ INDEX-HINT | NO-INDEX-HINT ]
)
|
For more information, see your OpenEdge DataServer Guides
(OpenEdge Data Management: DataServer for Microsoft SQL
Server and OpenEdge Data Management: DataServer for
Oracle).
- BREAK
- Over a series of query iterations, you might want to do some
work based on whether the value of a certain field changes. This
field defines a break group. For example, you might be accumulating
some value, such as a total. You use the BREAK option to define
customer.state as the break group, as shown:
OPEN QUERY q-order FOR EACH customer BREAK BY customer.state NO-LOCK.
|
When using the BREAK option you must also use
the BY option to name a sort field.
To test whether a break
group has changed, you can use the FIRST-OF( ) method and LAST-OF( ) method of
the query object handle.
- 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, the AVM uses that index to sort the records. Otherwise,
the AVM must presort the records before the first fetch when you
specify BY. The DESCENDING option sorts the records in descending
order (not in the default ascending order).
- COLLATE ( string , strength[ , collation] ) [ DESCENDING ]
- Generates the collation value of a string after applying a particular strength,
and optionally, a particular collation. The DESCENDING option sorts
the records in descending order (not in default ascending order).
- string
- A CHARACTER expression that evaluates to the string whose collation
value you want to generate.
- strength
- A CHARACTER expression that evaluates to an ABL comparison strength or an International
Components for Unicode (ICU) comparison strength.
The ABL comparison strengths
include:
- RAW — Generates a collation value for the string based on its binary
value.
- CASE-SENSITIVE — Generates a case-sensitive collation value for the
string based on a particular collation. If you specify this strength with an
ICU collation, the AVM applies the ICU TERTIARY strength.
- CASE-INSENSITIVE — Generates a case-insensitive collation value for
the string based on a particular collation. If you specify this strength with
an ICU collation, the AVM applies the ICU SECONDARY strength.
- CAPS — Generates a collation value for the string based on its binary
value after converting any lowercase letters in the string to uppercase
letters, based on the settings of the Internal Code Page
(-cpinternal) and Case Table (-cpcase)
startup parameters.
The ICU comparison strengths include:
- PRIMARY — Generates a collation value for the base characters in the
string.
- SECONDARY — Generates a collation value for the base characters and
any diacritical marks in the string.
- TERTIARY — Generates a case-sensitive collation value for the base
characters and any diacritical marks in the string.
- QUATERNARY — Generates a case-sensitive collation value for the base
characters and any diacritical marks in the string, and distinguishes words
with and without punctuation. ICU uses this strength to distinguish between
Hiragana and Katakana when applied with the ICU-JA (Japanese) collation.
Otherwise, it is the same as TERTIARY.
Note: Use ICU comparison strengths only with ICU
collations.
- collation
- A CHARACTER expression that evaluates to the name of an ABL collation
table or ICU collation. If collation does not
appear, COLLATE uses the collation table of the client.
- The
AVM reports an error and stops execution if one of the following
occurs:
- strength does not evaluate to
a valid value.
- collation does not evaluate to a collation table
residing in the convmap.cp file.
- collation evaluates to a collation table that
is not defined for the code page corresponding to the -cpinternal startup
parameter.
- INDEXED-REPOSITION
- If you specify this option, the AVM 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 an OpenEdge 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, the AVM 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; the AVM
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.
Example
The
following example opens a query on the Customer, Order, OrderLine, and
Item tables:
r-opqury.p
DEFINE QUERY q-order FOR Customer, Order, OrderLine, Item.
OPEN QUERY q-order FOR EACH Customer,
EACH Order OF Customer,
EACH OrderLine OF Order,
EACH Item OF OrderLine NO-LOCK.
GET FIRST q-order.
DO WHILE AVAILABLE Customer:
DISPLAY Customer.CustNum Customer.Name SKIP
Customer.Phone SKIP
Order.OrderNum Order.OrderDate SKIP
OrderLine.LineNum OrderLine.Price OrderLine.Qty SKIP
Item.ItemNum Item.ItemName SKIP
Item.CatDesc VIEW-AS EDITOR SIZE 50 BY 2 SCROLLBAR-VERTICAL
WITH FRAME ord-info CENTERED SIDE-LABELS TITLE "Order Information".
/* Allow scrolling, but not modification, of CatDesc. */
ASSIGN Item.CatDesc:READ-ONLY IN FRAME ord-info = TRUE
Item.CatDesc:SENSITIVE IN FRAME ord-info = TRUE.
PAUSE.
GET NEXT q-order.
END. /* DO WHILE AVAILABLE Customer */
|
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, the AVM 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.
- If you reopen a query to refresh the data displayed in a browse
widget, a selected row might not reflect the database's most current
data. To view the latest data in a selected row, add a FIND CURRENT
statement before the OPEN QUERY statement. This additional code
allows the AVM to put the current record into memory before executing
the OPEN QUERY statement.
- 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.
- A ProDataSet data-relation defined with REPOSITION or SELECTION
is overridden if a query is defined for a child relation data-source. Normally,
if REPOSITION is not specified, the child query selects the children
of the parent. But coding a query for the child data-source overrides whether
or not a SELECTION or REPOSITION relation mode was defined.
See also
CLOSE QUERY statement, CREATE QUERY statement, CURRENT-RESULT-ROW function, DEFINE BROWSE statement, DEFINE DATASET statement, DEFINE QUERY statement, GET statement, NUM-RESULTS function, QUERY-OFF-END function, QUERY-PREPARE( ) method, REPOSITION statement