OPEN and CLOSE QUERY statements

To get a query to retrieve data, you need to open it. When you open it, you specify the name of the query and a FOR EACH statement that references the buffers you named in the query definition, in the same order. If the query is already open, the AVM closes the current open query and then reopens it. This is the general syntax:

Syntax

OPEN QUERY query-name[ FOR | PRESELECT ] EACH record-phrase[ , ...]
  [ BREAK ][ BY phrase].

The syntax of the record-phrase is generally the same as the syntax for FOR EACH statements. If you use the PRESELECT EACH phrase instead of the FOR EACH phrase, all records that satisfy the query are selected and their row identifiers pre-cached, just as for a PRESELECT phrase in an ordinary data retrieval block. However, there are special cases for the record phrase in a query:

  • The first record phrase must specify EACH, and not FIRST, because the query is intended to retrieve a set of records. It is, however, valid to specify a WHERE clause in the record-phrase for the table that resulted in only a single record being selected, so a query can certainly have only one record in its result set. The record-phrase for any other buffers in the query can use the FIRST keyword instead of EACH if that is appropriate.
  • You cannot use the CAN-FIND keyword in a query definition. Doing so results in a compile-time error.
  • Queries support the use of an outer join between tables, using the OUTER-JOIN keyword, as explained below. FOR EACH statements outside of a query do not support the use of OUTER-JOIN.