Progress
Language Reference


DEFINE QUERY Statement

Interfaces
OS
SpeedScript
All
All
Yes

Defines a query that can be opened with an OPEN QUERY statement and from which records can be retrieved with a GET statement or BROWSE widget.

SYNTAX

DEFINE [ [ NEW ] SHARED ] QUERY query
  FOR bufname [ field-list ] [ , bufname [ field-list ] ] ...
  [ CACHE n ]
  [ SCROLLING ]
  [ RCODE-INFORMATION ] 

NEW SHARED QUERY query

Defines a query to be shared with one or more procedures called directly or indirectly by the current procedure. The called procedures must define the same query name as SHARED. For shared queries, each bufname must be the name of a shared buffer. The shared buffers must be specified in the same order both across shared queries and in the OPEN QUERY.

SHARED QUERY query

Defines a query that was initially defined by another procedure as NEW SHARED. For shared queries, each bufname must be the name of a shared buffer. The shared buffers must be specified in the same order across shared queries and in the OPEN QUERY.

QUERY query

Defines a query that can be used within the current procedure.

FOR bufname [ field-list ] [ , bufname [ field-list ] ] ...

Specifies the buffers to be used by the query, where bufname is a table or alternate buffer name. For a shared query, each bufname must be a shared buffer. If the query definition references more than one buffer, it defines a join.

Once the query has been defined, 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 field-list specifies a list of fields to include or exclude when you open the query. This is the syntax for field-list.

SYNTAX
{
      FIELDS [ ( [ field ... ] ) ]
   | EXCEPT [ ( [ field ... ] ) ] 
} 

The FIELDS option specifies the fields you want to include in the query, and the EXCEPT option specifies the fields that you want to exclude from the query. The field parameter is the name of a single field in the table specified by bufname. 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 each record in the query (returnable using the ROWID function). Specifying EXCEPT with no field references or specifying bufname without a field-list causes Progress to retrieve all fields for each record in the query.

This statement defines a query to retrieve only the name and balance fields from the customer table.

DEFINE QUERY custq FOR customer FIELDS (name balance). 

This statement defines a query to retrieve all fields of the customer table except the name and balance fields.

DEFINE QUERY custq FOR customer EXCEPT (name balance). 

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

CACHE n

Specifies the number of records of the query to hold in memory for a NO-LOCK query. Generally, caching more records produces better browse performance when accessing a database across a network. However, caching consumes both memory and CPU time for buffer management.

If you specify the CACHE option, the SCROLLING option is assumed. If a query is referenced in a DEFINE BROWSE statement, caching occurs by default. The default for a query involving only one table is 50 records. The default for a multi-table query is 30 records. If you specify CACHE 0 in the DEFINE QUERY statement, no caching occurs.

SCROLLING

Specifies that you can jump to a location within the list of records that satisfy the query by using the REPOSITION statement. If you do not use this option, you can use only the FIRST, NEXT, LAST, and PREV options of the GET statement to navigate within the list. Queries are faster if you do not use this option, but you must specify it to use the REPOSITION statement. For non-Progress databases, if you do not specify SCROLLING, you can only move forward through the list of records using the FIRST and NEXT options of the GET statement.

RCODE-INFORMATION

Assumes that the query is static, that an OPEN QUERY statement follows (though perhaps not immediately), and that you want to access the names of the indexes the query uses. This option tells Progress to copy the names of the indexes the query uses to the r-code’s initial value segment (IVS), from which you can access them by using the INDEX-INFORMATION method of the query object.

NOTE: If the query is dynamic, the RCODE-INFORMATION option does not apply, since Progress performs all query-related processing-compiling the query, determining the indexes the query uses, and providing access to the names of the indexes (through the INDEX-INFORMATION method of the query object)-at run time.

EXAMPLES

The following example defines two queries, q-salesrep and q-cust. The first is opened in the main procedure block and is used to find all salesrep records. The q-cust query is used to find all customers associated with a salesrep. The results of the q-cust query are displayed in a browse widget. The q-cust query is reopened each time you find a new salesrep.

r-defqry.p
DEFINE QUERY q-salesrep FOR salesrep
    FIELDS (salesrep.sales-rep salesrep.rep-name salesrep.region
            salesrep.month-quota).
DEFINE QUERY q-cust     FOR customer
    FIELDS (customer.cust-num customer.name customer.phone).

DEFINE BROWSE cust-brws QUERY q-cust
  DISPLAY customer.cust-num customer.name customer.phone
    WITH 5 DOWN TITLE "Customer Information".
    
DEFINE BUTTON b_next LABEL "Next".
DEFINE BUTTON b_quit LABEL "Quit" AUTO-ENDKEY.

FORM
   salesrep.sales-rep salesrep.rep-name salesrep.region
   salesrep.month-quota
   WITH FRAME rep-info SIDE-LABELS TITLE "Sales Rep. Info".
   
FORM b_next space(5) b_quit 
  WITH FRAME butt-frame COLUMN 60.

ON CHOOSE OF b_next
   DO:
      GET NEXT q-salesrep.
      IF NOT AVAILABLE(salesrep) THEN GET FIRST q-salesrep.
      RUN disp-rep.
   END.

OPEN QUERY q-salesrep FOR EACH salesrep NO-LOCK.
 
GET FIRST q-salesrep.
RUN disp-rep.

ENABLE cust-brws WITH FRAME cust-info.
ENABLE ALL WITH FRAME butt-frame.
  
WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW.


PROCEDURE disp-rep.                    
      DISPLAY salesrep.sales-rep
          salesrep.rep-name
          salesrep.region
          salesrep.month-quota
      WITH FRAME rep-info CENTERED SIDE-LABELS TITLE "Sales Rep. Info".
      OPEN QUERY q-cust FOR EACH customer OF salesrep NO-LOCK.
END PROCEDURE. 

The following example uses the RCODE-INFORMATION option of the DEFINE QUERY statement to extracts index information from a static query. If you run the example with the RCODE-INFORMATION option commented out, Progress reports a run time error.

r-rcdinf.p
/* r-rcdinf.p */
/* Extracts index information from a static query.*/

DEFINE QUERY q FOR customer RCODE-INFORMATION.
DEFINE VARIABLE h AS HANDLE. 

h = QUERY q:HANDLE.
OPEN QUERY q FOR EACH cust BY name.
MESSAGE h:INDEX-INFORMATION. 

NOTES

SEE ALSO

CLOSE QUERY Statement, CURRENT-RESULT-ROW Function, DEFINE BROWSE Statement, GET Statement, NUM-RESULTS Function, OPEN QUERY Statement, REPOSITION Statement, RUN Statement


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