Was this helpful?
Writing Different Types of Retrieve Statements
Several types of database query statements are possible in 4GL, allowing you to incorporate anything from a single item to an entire table on a form.
Using the Singleton Query
A singleton query gets values from a single row in the database table and places these values into simple fields on the form.
In the example below, the retrieve statement returns the single row that contains the part number in the partnum field.
editparts := retrieve (parts.name,
  parts.desc, parts.price)
  where parts.partno = partnum;
If more than one row in the database matches the conditions in the where clause, Ingres returns the first row that matches, as in this example:
empform := retrieve (emp.all)
  where dept = :dept;
Order is undefined unless you use the optional sort clause in the query. After a singleton query, rowcount is equal to at most 1.
Using the Table-Field Query
In a table-field query, Ingres retrieves a set of rows from the database into a table field on a form. The underlying data set of the table field receives all rows that match the query.
In this example, all the rows with an identification number greater than 100 are placed in the data set.
emptable := retrieve (emp.all)
  where idnum > 100;
Using the Attached Menu Query for Submenus
The attached query functions in QUEL as described for SQL. For example, the following query establishes a set of menu operations:
range of e is employee;
empform := retrieve(e.lname, e.age,
  e.empnum)
  where e.empnum < 10
begin
  "Msg" = 
  begin
      message "Now within submenu";
      sleep 3;
  end
  "Increment" = 
  begin
      age = age+1;
  end
  "NextRow" = 
  begin
      next;
  end
  "End" = 
  begin
      endloop;
  end
end
As Ingres displays each row of data, the user chooses whether to increment the age value, to go on to the next row, or to end. The endloop statement terminates the query.
Using the Master/Detail Query
The Master/Detail query functions the same way in QUEL as it does in SQL. The following example shows a QUEL Master/Detail database retrieval:
"Find" = 
begin
  empinfo := retrieve (empname = staff.name, 
    staff.title, staff.payrate)
  tasktable := retrieve (tasks.task,
    tasks.hours)
    where tasks.name = empname
  begin
    "Cancel" = 
    begin
      /* Code to cancel the task */
      next;
    end
    "Update Hours" = 
    begin
      /* Code to change hours on a task */
    end
    "Next" = 
    begin
      next;
    end
    "End" = 
    begin
      endloop;
    end
  end;
  clear field all;
end
In this example, the first query retrieves the master records into the empinfo form, and the second query retrieves the details for each master into the tasktable table field. The next statement steps through the master records. Each time a master row from the first query is displayed, the second query is run, based on the values from the empname column of the new master.
No semicolon is necessary between the two retrieve statements on the Master/Detail join, or between the second query statement and the begin statement (or starting brace) of the submenu.
Using a Retrieve Loop
The retrieve loop construction is analogous to the SQL select loop. Here is an example:
/* QUEL process employee update
 * transactions */

trans:= retrieve (h_trancode =
  transactions.trancode,
  h_empno = transactions.empno,
  h_salary = transactions.salary
begin
  /* Validate transaction */
  if (h_trancode = 1) then
    /* Salary update */
    repeat replace emp(salary = h_salary)
       where emp.empno = h_empno;
  else
    /* Process other transaction types */
  endif;
end;
Last modified date: 11/28/2023