Using Retrieve Loop

The retrieve loop allows the program to perform a computation for each retrieved row, rather than to display results to the user, as in the retrieve to a form or table field statement.

A retrieve loop retrieves and assigns multiple rows of data, one row at a time, usually to the local variables in the current form. A series of specified operations is performed once on the values in each row retrieved. The syntax for retrieve loop is as follows:

[identifier:] [repeat] retrieve [unique]
  (fieldname [[ rowexpression].columnname] =
      expression | table.column)
[where qual ]
[sort [by] sortlist ] | [order [by] sortlist ]
[ begin | {
  statementlist
end | }]

This form of the statement executes commands in the statementlist once for each row returned by the query. The value retrieved from the right-hand side of the target list is assigned to the variables in the left-hand side of the target list.

• The left-hand side, if present, must be a legal 4GL variable reference. This variable can be a simple field, a local variable, a table-field column, or a hidden column.

• If the left-hand side is not present, then the right-hand side must be a database table.column reference, in which case the column's name must be the same as a simple field or local variable to which the value is assigned.

During execution of the loop, the value of inquire_ingres (rowcount) is not defined; if a query statement is executed within the loop, then rowcount is defined for that statement. After the retrieve loop, rowcount equals the number of rows through which the program looped.

Retrieve loops perform an implicit next operation each time the loop is executed. An explicit next statement within the body of the loop causes the next row of the data to be retrieved. Statement execution continues with the statement immediately following next; the next statement does not cause the loop to be executed again from the top. At the end, the loop is terminated. Use the endloop statement to terminate the loop early. For more information, see Endloop.

The first statements are examples of the first variant of retrieve, retrieve to a form object. The last statement is an example of the second variant, the select loop, which uses the begin and end statements.

Retrieve information about an employee based on the value in the Empnum field:

Retrieve rows for all employees with an employee number greater than 100:

empform := retrieve (employee.lname,
employee.fname, empnum = employee.number)
where employee.number >100;

Retrieve into the Emptable table field all projects for the employee whose number is currently displayed in the Empnum simple field of the form:

Retrieve and sort employee names matching the qualifications of the Empdept and Empsal fields:

empform := retrieve (emplname =
    employee.lname, empfname = employee.fname)
  where qualification (employee.dept =
    empdept, employee.sal = empsal)
  sort by emplname:a, empfname:a
begin
  /* submenu code goes here */
end;

Retrieve values from the Projects table into the Projform form, in which the simple fields and database columns correspond:

Retrieve user input into a temporary table whose name is also based on user input, and call a report on the temporary table. The report frame, Orderreport, has an associated blank form. Arguments such as the table name are passed as parameters to the call report statement:

retrieve into :tbl (orders.all)
where qualification (status = status,
custno = custno);
call report (name = orderreport, table = :tbl);
destroy :tbl;

Use a retrieve loop with begin and end statements to perform an operation on the row matching the qualification criteria:

getemp: retrieve (emp.all)
  where qualification (emp.lname = lname)
begin
  /* statements, including queries, */
  /* which are executed once for every */
  /* row retrieved */
end;