Selecting into Table Fields
You can use several types of select statements with table fields. If the table field is in query mode, after the select is executed the table field is changed to update mode.
The following examples illustrate the use of select statements with table fields:
tablefield[] = select * from tablename
Selects into the current table field row. For example:
emptbl[] = select * from emp;
tablefield[] = select col, col from tablename
Selects into the columns, including hidden columns, of the current table field row. Columns not assigned are cleared. For example:
emptbl[] = select name, oldname =
name from emp;
tablefield[n] = select * from tablename
Selects into the indexed table field row. The row n must currently contain data (_state= 1 or more). For example:
emptbl[3] = select * from emp
where name = 'Jones';
tablefield[n] = select col, col from tablename
Selects into the columns, including hidden columns, of the indexed table field row. Columns not assigned are cleared. The row n must currently contain data (_state= 1 or more). For example:
emptbl[3] = select name, salary
from emp
where name = 'Smith';
select :tablefield.col1 = col2 from tablename
Selects into the columns, including hidden columns, of the current table field row. Columns not assigned are not changed. For example:
select :emptbl.salary = salary
from emp
where name = :emptbl.name;
select :tablefield[n].col1 = col1 from tablename
Selects into the columns, including hidden columns, of the indexed table field row. Columns not assigned are not changed. The row n must currently contain data (_state= 1 or more). For example:
select :emptbl[5].salary = salary
from emp
where name = 'Jones';
select :tablefield.col1 = col1, col2 from tablename
Selects into the columns, including hidden columns, of the current table field row and simple field or variable with one query. For example:
select :emptbl.salary = salary, name
from emp
where name = 'Smith';
select :tablefield[n].col1 = col1, col2 from tablename
Selects into the columns, including hidden columns, of the indexed table field row and simple field or variable with one query. The row n must currently contain data (_state= 1 or more). For example:
select :emptbl[5].salary = salary, dept
from emp
where name = 'Jones';
Selecting into Records
The following types of select statements are available for records:
record = select * from tablename
Selects from the columns of a database table into record attributes. Every attribute must have a corresponding column with the same name and data type in the table. Default values (blanks or zeroes) are loaded into record attributes for which there is no column of the same name, or whose data type is not coercible from the data type of the corresponding column.
The following example selects from the columns of the Employee table into the record emp_rec:
emp_rec = select * from Employee
record = select col [, col] from tablename
Selects from specified columns of a database table into corresponding record attributes. The contents of other attributes are not changed.
The following example selects from the Name and Job_title columns of the Employee table into the corresponding attributes of the record emp_rec:
emprec = select name, job_title
from Employee
select :rec.attribute = col [,:rec.attribute = col] from tablename
Selects from the column of a database table into a selected record attribute. The contents of other attributes are not changed. You must use a colon before the record name.
The following example selects from the Job_title column of the Employee table into the title attribute of the record emp_rec.
select :emprec.title = job_title from Employee
select :rec.rec.attribute1 = col [,:rec.rec.attribute = col] from tablename
Selects from a column of a database table into the attribute of a nested record (that is, a record that is an attribute of another record). The contents of other attributes are not changed. You must use a colon before the record name.
The following example selects from the City column of the Employee table into the city attribute of the address record. Address is an attribute of the record emp_rec:
select :emprec.address.city = city
from Employee
You can use the where and order by clauses with these select types.
Selecting into Arrays
To select into an array, use the following syntax:
array = select col1, col2 from tablename where ...;
If the query is executed successfully (even if no rows are returned), then previously allocated records are removed from the array before the retrieved rows are assigned to it, just as they are with table fields.
If you want to retrieve records into an array without clearing existing values, use a select loop to assign values to individual attributes in the array, as follows:
i = maxrow + 1 ;
select h_col1 = col1 [,h_col2 = col2] from tablename where ...
begin
array[i].attr1 = h_col1;
[array[i].attr2 = h_col2;]
i = i +1 ;
end
You must define h_col1, h_col2, and so on, as local variables of the appropriate data type. Each iteration of the select loop automatically appends a new record after the last record of the array. You receive a runtime error if you attempt to create empty records by increasing the index number (i) by any value greater than 1.
Note: Do not attempt to select a large number of rows into a table field or array because of memory limitations inherent in all computer systems.