Was this helpful?
RETRIEVE Examples
The following examples provide details.
Example 1:
The following example illustrates the use of retrieve to look up the name and salary of the employee number specified in host variable "numvar". Note the use of the null indicator variable with column "salary":
## range of e is employee
## retrieve (namevar = e.empname, 
## salvar:indvar = e.salary)
##   where e.empnum = numvar
Example 2:
In the following example, the constant "Name:" is assigned to result column "title", and the content of the "empname" column is assigned to host variable "namevar":
## range of e is employee
## retrieve (title = "name: ", namevar = e.empname)
##  where e.empnum >= 148 and e.age = agevar
Example 3:
The following example illustrates the use of retrieve to print information from the "employee" table for the employee whose number and name correspond to "numvar" and "namevar". Because this statement is issued many times (in a subprogram, perhaps), it is formulated as a repeat query. The "@" sign is required on only those variables substituting for constant values:
## repeat retrieve (empgrade = e.egrade, 
##  empsal = e.salary)
##  where e.eno = @numvar and e.ename = @namevar
Example 4:
The following example illustrates the use of retrieve to scan an entire table and generate a report. If an error occurs, the retrieve loop is aborted. No database statements are allowed inside the retrieve loop (within the curly braces).
Note the sort clause and the use of dereferenced variable names as result column names in the sort by clause:
error = 0
## range of e is employee
## range of d is department

## retrieve (empid = e.empnum, empname = e.#empname, 
##   empage = e.eage, empsal = e.salary, 
##   empdept = d.dname)
##  where e.edept = d.deptno
##  sort by #empdept, #empname
## {
 generate report of information
 if error condition then
   error = 1
## endretrieve
   end if
## }
## /* transferred here by completing the retrieval
## or because the endretrieve statement was issued
## */
  if error = 1
##  inquire_ingres (countvar = rowcount)
    print "error encountered after row", countvar
  else
    print "successful addition and reporting"
  end if
Example 5:
The following example illustrates the use of a string variable to specify the where clause:
run forms in query mode 
construct where_cond from user input on form
## range of employee is e
## retrieve ( empname = e.#empname, empid = e.empnum, 
##    empmgr = e.mgr ) where where_cond
## {
 load a table field with empname, empid, empmgr
## }
display table field for browsing
Last modified date: 01/30/2023