Select
Retrieves rows from one or more database tables.
Syntax
The select statement has four variants:
• Select to a complex object
• The Master/Detail query
• Select loop
• Select to simple objects
The syntax for each variant follows.
Select to a complex object:
complexobjectname := [repeated] select
[all|distinct]
[fieldname =] expression | [tablename.]columnname
{, [fieldname =] expression |
[tablename.]columnname}
from fromsource {, fromsource }
[where qual]
[group by columnname{, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] } ]
[begin | {
submenu
end | }]
Master/Detail query:
formname := select [all | distinct]
[fieldname =] [tablename.]columnname
{, [fieldname =] [tablename.]columnname}
from fromsource {, fromsource }
[where qual ]
[group by columnname{, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] } ]
tablefieldname = select [distinct]
[fieldname =] [tablename.]columnname
{, [fieldname =] [tablename.]columnname}
from fromsource {, fromsource }
where qual
[group by columnname{, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] }]
[begin | {
submenu
end | }]
Select loop:
[repeated] select [all | distinct]
[ [:]simpleobjectname =] expression |
[tablename.]columnname
{, [ [:]simpleobjectname =] expression |
[tablename.]columnname }
from fromsource {, fromsource }
[where qual]
[group by columnname {, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] } ]
begin | {
statementlist
end | }
Select to simple objects:
[repeated] select [all | distinct]
[ [:]simpleobjectname =] expression |
[tablename.]columnname
{, [ [:]simpleobjectname =] expression |
[tablename.]columnname }
from fromsource {, fromsource }
[where qual]
[group by columnname {, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] } ]
complexobjectname
Specifies the name of a form, table field, table field row, record, or array to which you are assigning values. Where the table-field name and form name are identical, specify the complexobjectname as formname.tablefieldname.
The data types of objectname and tablename.columnname or columnname must be compatible.
The current row of a table field can be written as tablefieldname[].
fieldname
Specifies the name of the object that receives the value from the specified database column:
• For a form, the name of a simple field
• For a table field or a row of a table field, a column in a table field
• For a record or array, an attribute of the record
expression
Specifies any legal 4GL expression whose type is compatible with simpleobjectname or fieldname to which expression is being assigned.
If the select statement refers to an expression rather than a columnname, the syntax is as follows:
• For a select into a complex object, the [fieldname =] preceding expression is required.
• For a select loop or select to a simple object, the [[:]simpleobjectname =] preceding expression is required, unless the expression is an asterisk (*) and all columns in the table correspond to fields on the form.
[tablename.]columnname
Specifies the name of the source column in the database table or tables from which data is selected. [tablename.] specifies the table containing the source column. It can be omitted if only one table in the from clause contains the source column. If the from clause specifies a corrname following the tablename, you must specify corrname.columnname instead of tablename.columnname.
fromsource
Specifies the name of the source of the columns from which data is selected. A source can be a table or an outer join between tables. A table is specified with the syntax:
[owner.]tablename [corrname]
A join between tables is specified with the syntax:
source join_type join source on search_condition.
where a source can be a table name or a join. For more information on the from clause, see the section, The From Clause.
qual
Specifies a logical expression of conditions that all rows selected must meet. It cannot include 4GL names. The qualification function is allowed as a condition. See The Qualification Function.
columnname
Specifies the column in the group by clause
orderfieldname
Specifies the name of a column on which to sort a 4GL name.
For a select into a complex object or master/detail query, orderfieldname must match:
• The fieldname in a fieldname =expression clause
• The fieldname in a fieldname = [tablename.]columnname clause
• The columnname in a [tablename.]columnname clause
For a select loop or select into simple objects, similar rules apply, except that the matching fieldname must be a simple field or simple variable, and can be preceded by a colon.
If more than one orderfieldname appears in an order by clause, the retrieved rows are sorted on the basis of the first, then on the second within the results of the first, and so on.
sortorder
Indicates whether the preceding orderfieldname is to be sorted in ascending (asc) or descending (desc) order. If omitted, asc is assumed. This is a 4GL name.
simpleobjectname
Specifies the name of a simple field, a local or global simple variable, a table-field cell, or a simple record attribute. The data types of the value and the object must be compatible.
Write table-field cells as :tablefieldname.columnname (which refers to the row under which the cursor is positioned) or :tablefieldname[integerexpression].columnname
Write the names of record attributes as :recordname.attributename
or
:arrayname[integerexpression].attributename
Where the table field name and the form name are identical, write the table field as formname.tablename.
If you specify a table-field cell or record attribute, a preceding colon is mandatory.
submenu
Specifies a separate menu displayed for the duration of a complex structure retrieval. It allows you to exercise some options with respect to the data displayed, including displaying the next row of data. Separate the submenu from the rest of the select statement by braces or by a begin and end pair.
Description
The 4GL select statement has the following variants:
• Select to a complex object assigns values from rows in a database table to a form, table field, table field row, record, or array. You can combine this with an assignment statement in a variety of query formats to perform different kinds of assignments to complex structures.
Attached queries (discussed below) can be nested to any level.
Submenus can be used with attached queries. They enable you to display the next master row in the data set, or to perform an operation on the current row.
• Master/detail query is a form of the attached query that selects data into the simple fields of a form (the master row) and also into a table field (the detail rows), then displays a submenu of operations that can be carried out for each master row selected.
• Select loop iterates through the rows of a query, processing them one row at a time. Each column of the row can be assigned to a simple field, simple variable, table field cell, or record attribute. This allows you to perform a sequence of 4GL statements for each row or record. More information on select loops is given in the section Using a Select Loop.
• Select to simple objects assigns values from a single row in a database table to various fields and/or simple variables.
When any one of these queries is executed, the query runs to completion, and the matching rows are stored in a temporary data set. The select statement then accesses the data from the data set.
For maximum performance when your code executes a particular query several times, use the reserved word repeated. Use the reserved word distinct to eliminate duplicate rows.
Limitations to the select statement:
• If the select statement whose target object is not a form returns no rows or records, it is unspecified whether the target object is cleared.
• Only the table's owner and users with select permission can select from a table.
If a where clause is included, the select statement returns values from the specified database table columns for all rows satisfying the where clause. The qualification function can be used in the where clause. You can store the where clause in a variable. However, in this case, you cannot use the qualification function.
Use the optional order by clause to sort rows alphabetically or numerically, in ascending or descending order.
See your query language reference guide for information on the use of the reserved words all, from, group by, and having.