21. 4GL Statement Glossary : Select : Select to Simple Objects
 
Share this page                  
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