Select to a Complex Object

The select variant select to a complex object is always associated with a query assignment statement. The select statement is a database expression, because it is equivalent to the values it selects. The syntax for this variant is shown in the main syntax section for the select statement above.

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

The select variant select to a complex object can be further subdivided into three types:

These are discussed in the following sections.

A singleton query gets values from the first (or only) row in the data set retrieved from the database table and places these values in simple variables and simple fields of the current form, columns of a row of a table field, or attributes of a record, without the use of a submenu. The query stops after the first row, and only one row is returned.

The following singleton query selects a row of values from the Personnel table into a form named Deptform:

The following singleton query selects into a record in an array:

This example selects the same row of values as in the previous example, but assigns them to the first record in the array deptarray. This example assumes that all attributes of deptarray have corresponding columns with the same names in the Personnel table.

A table-field query or array query selects rows of a table from the database into a table field on a form or into an array, without the use of a submenu.

In a table‑field query, the maximum number of rows that fit within the table field are displayed, and the remainder are held in a buffer (the underlying data set) by the FRS. You can view all the rows selected by scrolling through the table field.

The following table-field query retrieves values from the projects table into a table field. Columns in the emptable table field correspond to columns in the Projects table.

The following array query retrieves values from the Projects table into an array. Attributes in the emparray correspond to columns in the Projects table.

emparray := select projects, hours
from projects
where empnum := empnum;

A simple attached query selects several rows of data to a form, table-field row, or record. When used with a form, a simple attached query performs these functions:

• It displays the first row in the simple fields of the form and uses a submenu to provide access to each of the subsequent rows.

• It displays a submenu of operations that can be carried out for each returned row that is displayed.

• It provides a submenu operation that invokes a next statement for displaying subsequent rows.

Submenus are discussed in more detail later in this section. Refer also to the section Next. An example of a simple attached query is shown below:

'Find' =
begin
  editparts := select partname = partname,
    partno = partno, descr = descr,
    cost = cost
    from part
    where cost < :mincost
  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

When you choose Find, the application displays on the form the first row selected, along with this submenu:

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

You can determine the number of database rows processed by the attached query through the use of the rowcount reserved word in an inquire_sql statement following a database access. The example below assigns the number of rows accessed to the field called rows:

The rowcount constant is undefined while the submenu is active. After the submenu closes, rowcount equals the first row plus the number of rows displayed with a next statement. After a singleton query, rowcount has a value of 1.

Using attached queries with table-field rows and records is similar to using them with forms, except that the rows are assigned to table-field columns and attributes of a record instead of fields of a form.