Select to Simple Objects
Select to simple objects allows you to retrieve a single row from a database table and place the columns of that row into specified simple fields, simple variables, table field cells, or record attributes. The syntax for a select to a simple object is as follows:
[repeated] select [distinct]
[ [:]simpleobjectname =] expression |
[tablename.]columnname
{, [ [:]simpleobjectname =] expression |
[tablename.]columnname }
from tablename [corrname] {, tablename [corrname] }
[where qual]
[group by columnname {, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] } ]
The behavior of select to a simple object is similar to that of a singleton query, but the syntax is different. For example:
select namefield = empname,
workvariable = emp_hire_date
from employee
where empnum = :empnumfield
In this example, the employee record for the employee whose number is in empnumfield is retrieved from the Employee table. The column empname is placed directly on the form in namefield. The column emp_hire_date is placed in the variable workvariable.
Examples
Use select to a complex object to select information about an employee based on the value in the Empnum field; then use the commit statement to end the transaction:
deptform := select lname = last,
fname = first
from personnel
where empnum = :empnum;
commit;
Use select to a complex object to select rows for all employees with an employee number greater than 100; then use the commit statement to end the transaction. The table name is held in a variable:
tbl := 'employee';
empform := select lname, fname,
empnum = employee
from :tbl
where number >100;
commit;
Use select to a complex object to read into the table field Emptable all projects for the employee whose number is currently displayed in the Empnum simple field of the form; then use the commit statement to end the transaction:
emptable := select project, hours
from projects
where empnum = :empnum;
commit;
Use select to a complex object to select and sort employee names matching the qualifications of the empdept and empsal fields; then use the commit statement to end the transaction:
empform := select emplname = lname,
empfname = fname
from employee
where qualification (dept = empdept,
sal = empsal)
order by emplname asc, empfname asc;
begin
/* submenu code goes here */
end;
commit;
Use select to a complex object to read values from the Projects table into the Projform form, in which the simple fields and database columns correspond; then use the commit statement to end the transaction:
projform := select *
from projects
where empnum = :empnum;
commit;
Use a select loop with begin and end statement to perform an operation on the row matching the qualification criteria. Use the commit statement to end the transaction:
select * from emp
where qualification (lname = lname)
begin
/* statements, including queries, */
/* which are executed once for every */
/* row retrieved */
end
;
commit;
Use a select loop to a append records to an array:
/* name = char(...), salary = integer */
i = maxrow + 1;
select name, salary
from emptable
begin
emparray[i].name = name;
emparray[i].salary = salary;
i = i + 1 ;
/* other statements here */
end;
Use a query to sort by the result (field) name, person, and not by the column name Ename.
newform := select person=ename, dept
from employee
order by person;
Use a singleton select to a record, selecting a row from the table Emp to the record r_emp:
/* r_emp = type of table employee */
r_emp = select * from employee
where lname = 'Smith';
Select to an Array: Example
This example is a simple application which performs a search by reading a table into an array and searching the array. You enter a part name and the application returns the part number, if the part is found. The array is stored in memory. It is faster to search the array than the database table.
In the initialize section of the PartMenu, the only frame, the procedure SelectParts() is called. This selects the entire parts table into an array of type of table part, sorting it by partname.
When you choose Find, the only menu option, the application prompts for a part name, then calls the procedure GetPart(). GetPart() calls the procedure Binary_Search() to search the array. Binary_Search() returns the position of the array row containing the part in question, or 0 if that part was not found.
PartMenu then looks up the part number in the row returned by GetPart().
The application assumes that the data is not updated while you are searching for parts. The table is part of a read‑only or rarely‑updated database intended mainly for reference. If the data is likely to be updated by one user while another is doing a search, the application uses a time stamp. The time stamp must be stored in a database table and must be accessible to users updating and searching the Part table.
The source code for PartMenu and the procedures described above follow.
This is the 4GL code for PartMenu, the top frame of this application:
initialize = declare
parttable = array of type of table part;
name = char(16);
pos = smallint;
begin
callproc selectparts
(parttable = parttable);
end
'Find' =
begin
name = prompt 'Enter part name: ';
pos = callproc getpart
(parttable = parttable, name = name);
if pos = 0 then
message 'This part is not in the table'
with style = popup;
else
message 'Part ' + :name + ': part # = ' +
varchar(parttable[:pos].partno)
with style = popup;
endif;
end
'End', key frskey3 =
begin
return;
end
This is the 4GL code for the procedure SelectParts(). This procedure selects the entire part table into an array, sorted by partname.
/* Input: parttable: the array to be selected into */
procedure selectparts ( parttable =
array of type of table part ) =
begin
parttable = select * from part
order by partname;
end
This is the 4GL code for the procedure GetPart(). This procedure retrieves a particular part from an array of parts. You enter the name of the part to be found. GetPart() returns the position of the part within the array, or 0 if the part is not found.
procedure getpart ( parttable =
array of type of table part,
name = char(16)
) =
declare
nrows = smallint;
pos = smallint;
begin
nrows = callproc arrayallrows( parttable);
pos = callproc binary_search
(arr = parttable, name = name, first = 1,
last = nrows);
return pos;
end
This is the 4GL code for the procedure Binary_Search(). This procedure performs a binary search on an array of parts. Binary_Search() returns the position of the part within the array, or 0 if the part is not found.
/* Inputs: */
/* arr - the array to be searched */
/* name - the part name to be found */
/* first, last - the beginning and end */
/* of the array section being searched */
procedure binary_search ( arr =
array of type of table part,
name = char(16), first = smallint,
last = smallint
) =
declare
pos = smallint;
middle = smallint;
begin
if arr[first].partname = name then
pos = first;
elseif arr[last].partname = name then
pos = last;
elseif last - first <= 1 then
pos = 0; /* not found */
else
middle = first + (last-first)/2;
if arr[middle].partname <= name then
pos = callproc binary_search(arr = arr,
name = name, first = middle,
last = last);
else
pos = callproc binary_search(arr = arr,
name = name, first = first,
last = middle);
endif;
endif;
return pos;
end