Next/Previous Next/Previous

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

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

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

Next/Previous Next/Previous
Preliminary Draft
© 2017 Progress Software Corporation and/or one of its subsidiaries or affiliates. All rights reserved.