The Retrieve Statement
To read data from database tables into forms and fields, use the retrieve statement. This statement takes values from the columns in the specified database table for all rows that satisfy the condition specified in a where clause. When you intend to get only a single row from the query statement (for example, in assigning values to the simple fields in a form), use the where clause to specify the row uniquely, as in this example:
deptform := retrieve (personnel.lname,
personnel.fname)
where personnel.empnum = 7;
This statement retrieves the name of employee number 7.
If you are retrieving many rows (as in a table-field assignment), use a where clause that specifies several rows, as shown in this example:
perstable := retrieve (personnel.lname,
personnel.fname, personnel.salary)
where personnel.salary >= 20000;
This statement retrieves a list of personnel who make at least $20,000.
Specify sorting criteria when the retrieval applies to several rows. Use the keywords sort by:
emptbl := retrieve (person =
employee.ename, employee.dept)
sort by person;
This example sorts a list of employees and their departments by employee name.
To specify sorting criteria and order, add ":a" (ascending—the default) or ":d" (descending). For example, to sort the above list in descending order by the name of the employee, specify the sort clause by:
sort by person:d;