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.
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.