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:
• The singleton query
• The table field or array query
• The simple attached query
These are discussed in the following sections.
Singleton Query
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:
deptform := select lname, fname
from personnel where empnum = 7;
The following singleton query selects into a record in an array:
deptarray[1] := select *
from personnel where empnum = 7;
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.
Select to a Table Field or Array
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.
emptable := select project, hours
from projects
where empnum = :empnum;
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;
Simple Attached Query and Submenu
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 attaches the as-yet-undisplayed rows to the form.
• 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:
Change Delete IncreasePrice Next End
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:
inquire_sql (rows = rowcount);
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.