Was this helpful?
Retrieve Statement
In EQUEL, the retrieve statement returns data to a set of host language variables. In EQUEL programs, the retrieve statement is normally followed immediately by a block of program code enclosed by the delimiters "##{" and "##}". At runtime, the program retrieves a row into host variables and executes this block of code once for each row of data retrieved. If no rows are retrieved, the code block is not executed. The retrieve loop normally terminates after all rows have been processed.
You can terminate the loop before all rows are retrieved, using the endretrieve or endloop statements. You must not use a host language goto statement to exit the loop; if you do, the next database access statement causes an error.
Retrieve loops must not include other statements that access the database. When the retrieve loop terminates, control passes to the statement following the retrieve loop.
The following example illustrates the use of retrieve loops. This example retrieves a collection of rows, containing an employee's name, salary, and manager's name. For each row, the program statements in the retrieve loop compute and print the ratio of the employee's salary to the manager's.
The program processes at most 10 rows, and executes an endretrieve statement when the loop counter exceeds 10.
begin program
## ename            character_string(21)
## mname            character_string(21)
## salary, msalary  float
## eno, n           integer
## ingres personnel
   n = 0
## range of e is employee
## range of m is employee
## retrieve (ename = e.empname, salary = e.#salary,
##   mname = m.empname, msalary = m.#salary)
## where e.manager = m.empnum
## {
## n = n + 1
## if n > 10  
##       endretrieve
   else
      print ename, salary, mname, msalary
   end if
## }
## exit
end program
The value from the salary column is automatically converted from money, as it is represented in the database, to floating point, as it is stored in the program variable.
The retrieve statement can be formulated as a repeat query, thus reducing the overhead required to run the same query repeatedly within an application. For more information, see Repeat Queries.
Last modified date: 01/30/2023