Retrieve (QUEL)
Retrieves rows from a database table.
Syntax
objectname := [repeat] retrieve [unique]
([fieldname = expression] | tablename.columnname
{, [fieldname = expression] |
tablename.columnname})
[where qual ]
[sort [by] sortlist] | [order [by] sortlist ]
[begin | {
submenu
end | }]
objectname
Specifies the name of a form or of a table field in a form to which values are assigned. Where the table field name and form name are identical, specify objectname as formname.tablefieldname. Data types of objectname and tablename.columnname must be compatible.
fieldname
Specifies the name of a simple field on the form or of a column in the table field that receives the value from the specified database column
expression
Specifies any legal 4GL expression. The data types of the expression and fieldname must be compatible.
tablename.columnname
Specifies the name of the source table and column in the database table from which data is retrieved.
The name of the table cannot also be the name of a field on the form or a variable.
qual
Specifies a logical expression indicating conditions all rows retrieved must meet. It cannot include 4GL names. The qualification function is also allowed.
sortlist
Specifies a list of simple field names or table-field column names to serve as sort criteria. If more than one name appears in the list, 4GL sorts on the basis of the first, then on the basis of the second within the results of the first, and so on. To sort in ascending or descending order, append asc or desc to a field name after a colon (as in namefield:desc). You need not specify the default, asc.
submenu
Specifies a separate menu displayed for the duration of a retrieval. It lets the user exercise some options with respect to the data displayed, including displaying the next row of data. Separate the submenu from the rest of the retrieve statement by braces or by a begin and end pair.
Description
The retrieve statement has three variants:
Retrieve to a form or table field
Assigns values from rows in a database table to a form or a table field on a form. Combine this variant with an assignment statement in a variety of query formats to perform different kinds of assignments to forms or table fields. Attached queries (discussed below) can be nested, one within the other, to any level. The syntax for this variant is shown above.
Submenus can be used with attached queries only. They provide the user with the capability of displaying the next master row in the data set, or of performing an operation on the current row.
Retrieve into
Creates a new table and fills it with rows from other tables in the database that result from a query. The syntax for this variant is shown in its own section below.
Retrieve loop
Iterates through the rows of a query, assigning them to a form one row at a time to perform a sequence of 4GL statements for each row. The syntax for this variant is shown in its own section below.
For maximum performance, retrieve to a form object and retrieve loop can be repeated, using the reserved word repeat. You cannot repeat the retrieve into variant of this statement. Use the reserved word unique to eliminate duplicate rows.
Limitations to the retrieve statement:
• If the retrieve statement returns no rows, simple fields and table fields are not cleared. If it is necessary to clear these fields, issue a clear field statement before the retrieve.
• Only the table's owner and users with retrieve permission can retrieve from a table.
If a where clause is included, the retrieve statement returns values from the specified database table columns for all rows satisfying the where clause. The qualification function can be used in a where clause. Store the where clause in a variable. However, in this case, you cannot use the qualification statement.
Queries in which the qualification function is used are not suitable for use as repeated queries. The keyword repeat is ignored if you use it with such queries.
Use an optional sort or order clause to sort rows alphabetically or numerically, in ascending or descending order. The only difference between the two is that the sort clause removes duplicate rows, while the order clause does not.
These clauses are discussed in more detail in the following sections.
Last modified date: 11/28/2023