Programming Guide : 5. Working with a Database : How You Can Access a Database with Standard SQL Statements : Select Statement
 
Share this page                  
Select Statement
The select statement lets you retrieve data from the database into:
Simple variables
Reference variables
Array variables
If the variable receiving data is associated with a field on a form that is currently displayed, the data is displayed in the field as it is assigned to the variable.
A select statement can retrieve:
A single row
A select statement that retrieves only one row is called a singleton select. The select statement deposits the retrieved data into variables that the program can process as needed.
Multiple rows
When a select statement returns more than one row, only the first row returned is visible to the program. To access the additional rows, use one of the following constructs:
Select loop
A select loop is a block of 4GL statements that are performed on each row returned by the select statement. The loop ends when there are no more rows or an endloop statement is encountered. Select loops are the simplest and often most efficient way to handle multiple rows.
Cursor
A cursor is a row marker that designates an individual row in the select statement's result table (the set of rows returned by a non-singleton select). As the program moves the cursor forward through the result table, you can update or delete the row to which the cursor points and use the values in the row.
For a detailed discussion of cursors in OpenROAD and examples of their use, see How You Can Use Cursors to Access the Database.
Example--Singleton Select
The following example represents a singleton select. Given an account number, it selects information about a customer from a table that contains customer information:
declare
    cname = varchar(100) not null;
    cphone = varchar(10) not null;
enddeclare
{
     ...
/*
**/Given an account number, get customer information
*/
select cname as   :cname,
       cphone as  :cphone
from customer
where acctnum = :account_number;
...
}
Example--Select Loop
Suppose that cust is a class with attributes that correspond to columns in the customer table. The following select loop extracts information from the customer table and stores it in an array of cust objects:
declare
    cust_arr        = array of cust;
    i               = integer not null;
enddeclare
{
...

i = 1;
select         acctnum    as cust_arr[I].accnt,
               cname      as cust_arr[I].name,
               cphone     as cust_arr[I].phone

    from       customer
    order by   cname
    {
               i = i + 1;
    };
    ...
}
How You Can Select Data into Reference or Array Variables
Regardless of whether you retrieve one row or multiple rows, when you retrieve data into a reference or array variable, the select statement must see the individual attributes of the variable rather than the variable as a whole.
How You Can Use the Select Statement Generically
You can use a standard select statement when you do not know the database table until runtime, provided you know the column names. Assume, for example, that you have several identically structured tables—po91, po92, and po93—that store purchase order information. You can write a single select statement to access all the purchase order tables as follows:
1. Use a variable to reference the table name.
You must notify the DBMS to interpret the variable representing the table name by preceding the variable with a leading colon (:).
2. Supply the table name at runtime.
The following statement selects data from table tblname into known fields on the form. The actual table name is substituted at runtime.
select ponum as :field1, podate as :field2,
vendorname as :field3
from :tblname;
If you also do not know the names of the columns until runtime, you can build a valid select statement using the execute immediate statement. However, you must know the name and data type of the variables that are to receive the data. If you do not have the necessary information about the target variables, create an SQLSelect object or a query object to build the retrieval statement dynamically.
For more information about dynamic query creation, see How You Can Access a Database with DataStream Objects and How You Can Use Query Objects. For more information about the execute immediate statement, see the Language Reference Guide.