The query assignment statement assigns a series of values from the database to several or all simple fields on a form, to the columns of a table field or table field row, or to the attribute of an array or record. In a query assignment, you use a select statement. You can optionally include a submenu.

A query assignment statement to a form uses a query to assign a list of values from the database to one or more simple objects--a simple field on the form, a simple local variable, or a simple component of a complex object. Query targets for query assignment to a form are the same as those for direct assignment. For this type of assignment, specify the form name instead of a field name on the left of the assignment statement. For example:

In this case, empform is the form associated with the frame. It contains simple fields corresponding to projname and hours in the Projects database table. The select statement is the query that causes Ingres to read the data into the fields on the form.

The target simple objects can have names that are different than the database columns, but they must match in data type. If the names are different, you must use both of them in the assignment statement as shown below. The following statement retrieves values from Projname to the Task field, while the values from Hours are retrieved to the Time field.

As a simple field assignment, this statement reads one record from the database table.

If you specify both a target simple object and database column, then you can precede the target simple object with a colon (:). If the target simple object is a simple component of a complex object, you must specify the target object by a qualified name and precede it with a colon (:).

In a query assignment to a table field or array, the query targets are the array's attributes or the table field's columns. If the query is successfully executed, the previous contents of the table field or array are cleared, and the results of the query become the new contents. By default, the table field or array is cleared even if the query does not return any rows. Retain the previous contents if the query does not return any rows. Use set_4gl (clear_on_no_rows=off) to turn off the default behavior. See Set_4gl.

In the following example, the select query reads multiple records selected from the database table parts into a data set associated with the table field partstbl.

This example might result in the display of the following number, name and price items:

4GL responds by creating a data set from the results of the query, and displaying as many rows as the table field can accommodate, as defined through the ABF FormEdit operation. You can scroll through all the records in the data set by using the mapped cursor keys to display subsequent rows or by scrolling programmatically.

In a query assignment to a record, table field row, or array record, the query targets are the attributes of the array or record or the table field's columns. This type of assignment is a singleton query; it reads one row from the database.

If the select statement is executed successfully, all columns or attributes are cleared before the values are returned from the database. Default values (zeroes or blanks) are placed into any column or attribute for which no value is returned.

To select values into specific columns or attributes without clearing others, use the syntax for selecting to simple objects (as shown in the last example in this section), instead of specifying a query assignment to the record, table field row, or array record.

If the select statement does not return any rows, do not clear all the columns or attributes. By default, the columns or attributes are cleared even if the query does not return any rows. However, you can use set_4gl (clear_on_no_rows=off) to turn off the default behavior. See Set_4gl.

The examples below show similar query assignments to an array record and to a table field row. Note the differences in syntax.

The columns name, job_title, and ssno are read from the Employees table for the record whose value for ssno is "555-55-5555." They are read into the table field columns name, job_title, and ss for the current row of the table field. The cursor must be in the table field for this query to succeed. This row of emptbl has another attribute, salary, which is cleared.

In this example, the columns name, job_title, and ssno are read from the Employees table for the record whose value for ssno is '555-55-5555'. They are read into the attributes name, job_title, and ss for the first record in the array emparr. This array has another attribute, salary, which is set to 0.

The following example shows assignment from a query to attributes of an array. It reads the columns name, job_title, and ssno without clearing the salary column.

The columns name, job_title, and ssno are read from the Employees table for the records whose value for ssno is "555-55-5555." They are read into the attributes name, job_title, and ss for the first record in the array emparr. The other attributes in emparr retain their previous values.

The select statement is described further in Select.