Using Retrieve to a Form or Table Field

The variant retrieve to a form or table field is always associated with an assignment statement. The retrieve statement itself is a database expression, because it is equivalent to the values it retrieves. The syntax for retrieve to a form or table field is shown below:

objectname := [repeat] retrieve [unique]
  ([fieldname = expression] | tablename.columnname
  {, [fieldname = expression] |
  tablename.columnname})
[where qual ]
[sort [by] sortlist] | [order [by] sortlist ]
[begin | {
  submenu
end | }]

The retrieve statement makes up the right side of an assignment statement, while the name of the form or table field to which the retrieved values are assigned appears on the left side. If the retrieve returns no rows, then the previous contents of the field into which you are retrieving the data do not change.

There are four types of retrievals to a form or table field: the singleton query, the table-field query, the simple attached query, and the Master/Detail query. The latter two are attached queries, which use a form of the retrieve statement that includes a submenu. See Submenus.

A singleton query retrieves and assigns a single row of values solely to the simple fields of the current form, without the use of a submenu. The query is stopped after the first row is retrieved, and only one row is displayed.

deptform := retrieve (personnel.lname,
personnel.fname)
where personnel.empnum = 7;

A table-field query retrieves a set of forms and table fields from the database into a table field on a form, without the use of a submenu. The maximum number of rows that fit within the table field are displayed, and the remainder are buffered in the data set by the FRS. The user can view all the rows retrieved by scrolling through the table field.

emptable = retrieve (projects.project,
projects.hours)
where projects.empnum = empnum;

A simple attached query retrieves and assigns several rows of data to the simple fields of a form. A simple attached query performs these functions:

• It provides submenu operations for displaying subsequent rows through a next statement. Submenus are discussed in more detail later in this section. See also Next.

"Find" =
begin
  editparts := retrieve (partname =
    part.partname, partno = part.partno,
    descr = part.descr, cost = part.cost)
    where part.partno > 2666
  begin
    "Change" =
    begin
      /*code to replace the database entry */
      /*for the part with the new values */
      next;
    end

    "Delete" =
    begin
      /*code to delete the displayed part */
      /*from the database */
      next;
    end
    "IncreasePrice" =
    begin
      cost = cost * 1.1;
    end
    "Next" =
    begin
      next;
    end
    "End" =
    begin
      endloop;
    end
  end;
  message "Done with query";
  sleep 2;
  clear field all;
end

Change Delete IncreasePrice Next End

A single retrieve statement both starts the database retrieval and displays the new list of operations. 4GL combines these two actions within the statement because they are typically tied together in forms-based applications.

A Master/Detail query is a form of the attached query that retrieves data into the simple fields of a form (the master row) and also into a table field (the detail rows), then displays a submenu of operations that can be carried out for each master row retrieved. The first statement is the parent or master query; the second is the child or detail query.

Inclusion of a submenu offers the user a choice of operations that can be carried out for each master row retrieved, and provides the means for displaying the next master row. If the submenu is omitted, a single master row and all the details for that master are displayed at once; there is no way to see the next master row.

formname := [repeat] retrieve [unique]
    ([fieldname =] tablename.columnname
    {, [fieldname =] tablename.columnname})
[where qual ]
[sort [by] sortlist] | [order [by] sortlist ]
[formname.]tablefieldname :=
    [repeat] retrieve [unique]
    ([fieldname =] tablename.columnname
    {, [fieldname =] tablename.columnname})
[where qual ]
[sort [by] sortlist] | [order [by] sortlist ]
[begin | {
    submenu
end | }]

The only difference between this syntax and that of a simple attached query is that two queries are specified rather than one. Note that there is no semicolon between the two assignment statements. Only two assignment statements are allowed, and each must be to a form or table field.

"Find" = begin addorders :=
  retrieve (orders.orderno,
    customer.custname)
    where qualification (orders.orderno =
    orderno, customer.custname = custname)
    and orders.custno = customer.custno
  partstbl := retrieve (part.partname,
    orderitems.qty, part.cost)
    where part.partno = orderitems.partno
    and orderitems.orderno = orderno
  begin
    "Cancel" = begin
      /* code to delete the order */
      next;
    end
    "Old" = begin
      /* code to change the order status */
      /* to old */
    end
    "Back" = begin
      /* code to change the order status */
      /* to back
    end
    "Next" = begin
      next;
    end

    "End" = begin
      endloop;
    end
  end;
  clear field all;
end

Each time a master row from the first query is displayed, the second query is run, based on the values from the new master. Once the master and the detail rows are displayed, 4GL prints this submenu on the window:

Cancel Old Back Next End

In a Master-Detail query, the where clause for the detail query is used to specify how the two queries are joined together. For instance, the detail query in the example shown above includes a reference to the orderno field, whose value was retrieved during the first query.

The value of the rowcount Ingres constant is always the number of master rows retrieved, whether or not a submenu is included. For a Master/Detail query with a submenu, the count is defined after the submenu ends and is set to the number of master records that were retrieved. The only exception to this is nested queries within submenus. See the section, Nesting Attached Queries.

A submenu is a separate menu of choices that appears following the display of each master row in the retrieved data set for an attached query. Choosing an operation from the submenu causes the designated action to be performed, such as incrementing a salary field or displaying the next master row. Submenu operations always apply to the master row.

When a query with a submenu is executed, the query runs to completion, and a data set is created in a temporary file to hold the retrieved rows. A submenu appears at the bottom of the frame. Each next statement (associated with an item on the submenu) causes the next row in the data set to be displayed. If this is a Master/Detail query, the master query is performed as above, and another query runs to completion to retrieve the matching detail rows for each master row.

objectname := retrieve statement
[objectname := retrieve statement]
begin | {
  initialize = begin | {
    /* statements executed before first row */
    /* is displayed */
  end | }
  "menuitem1" = begin | {
    /* code for menuitem1 operation */
  end | }
  "menuitem2" =  begin | {
    /* code for menuitem2 operation */
  end | }
end | }

The submenu is separated from the rest of the retrieve statement by braces or by a begin and end pair. In the syntax of a Master/Detail query, the submenu appears after both assignment statements.

Submenus can contain all the same components as main menus. Generally, at least one of the menu operations should contain the 4GL next statement, which causes the next row retrieved to be displayed.

The user stays within the submenu until either (1) a next statement is executed after all the rows have been displayed, or (2) an endloop statement is executed. When either situation occurs, control returns to the statement immediately following the assignment statement that contained the submenu.

The following example of a simple attached query retrieves a series of row-typed values from the database and creates a submenu for the current form:

range of e is employee;
empform := retrieve (e.lname, e.age, e.empnum)
  where e.empnum <=10
begin
  initialize =
  begin
    /* Statements here are executed once */
    /* before the first row is displayed */
    message "Now within submenu";
    sleep 3;
  end
  field "lname" =
  begin
    message "You just entered value: " +
      lname;
    sleep 3;
    resume next;
  end
  "Increment" =
  begin
    age := age + 1;
  end

  "NextRow" =
  begin
    next;
  end
  "End" =
  begin
    endloop;
  end
end;

When the user chooses an operation in the main menu containing this assignment statement, the following submenu appears at the bottom of the window:

Increment NextRow End

The first row is retrieved in the three fields specified. Each time the user chooses the NextRow operation from the submenu, the next row is displayed. When there are no more rows, the message "No more rows" appears at the bottom of the frame, and the user exits from the current submenu. The main menu for the frame is again displayed.

If no rows in the database satisfy the query, the message "No rows retrieved" appears at the bottom of the frame, and control is transferred to the statements immediately following the assignment containing the submenu.

The submenu definition can contain any legal operations, including menu, key, and field activations. The first submenu operation definition can be an initialize block. Unlike the initialize block for a frame, the initialize block in an attached query cannot contain variable declarations. The initialize block for the attached query is executed only once, after the master row and matching details have been selected from the database and before their values have been displayed. Both 4GL and QUEL statements can appear in the initialize block.

The code in the submenu can contain any legal 4GL code. This includes query statements, such as replace and delete, as well as other assignment statements involving retrieves. A return or exit statement within the submenu has the same effect as a corresponding statement from the main menu of the frame. The resume and validate statements operate in the same manner as they do in the main menu; however, if they close the current operation, the submenu is still displayed.

While the submenu is running, the value of the rowcount FRS constant is undefined. Rowcount only has a value when the submenu ends. The single exception is when a query statement in the submenu code is executed, then rowcount is set for that query statement.

Note that when the assignment statement containing the submenu is executed, Application By Forms displays the current form in update mode. This mode can be changed in the initialize section of the submenu. When the end user exits from the submenu, the display mode in effect prior to the submenu again takes effect.

4GL provides the capability to nest attached queries to any level. Each nested query is placed within the submenu of the higher-level query. Thus, the following syntax is valid:

formobject := database_expression
begin
  "menuitem" =
  begin
    formobject := database_expression
    begin
    {submenu}
    end
  end
end;

For nested queries with a submenu, the value of the rowcount FRS constant is undefined while the submenu is running. Rowcount only has a value when the submenu ends. The single exception when a query statement in the submenu code is executed; then the rowcount is set for that query statement.