This statement retrieves rows from a database table.

This statement has the following syntax:

where subselect has the following syntax:

and fromclause has the following alternative syntaxes:

and groupclause has the following alternative syntaxes:

and orderclause has the following alternative syntaxes:

You can use the select statement in two ways:

If you retrieve multiple rows, you can use either a select loop or a cursor to process them. Use the keyword, distinct, to eliminate duplicate rows from the result table.

The optional repeated keyword directs the database to encode and save the query execution plan. This feature is a performance benefit if you plan to execute this query more than once in the application. Do not use the repeated option if you use dynamic names for tables or columns, or use a single variable for the searchcondition specification in the statement.

When you want to retrieve only one row or want to use a select loop to process multiple rows, use either of the following formats for the value of resultexpression:

or

If you intend to use a cursor to process the rows, the select statement is part of an open cursor statement. Alternatively, if you are coding the select as the subselect of an insert statement, use one of the following formats for the value of resultexpression:

If you are using a cursor to process the selected rows, you must use the fetch statement to access the returned values. If you retrieve values into a resultname, use this name as the value of columnname in the variable list of your fetch statement.

For more information about using a select statement with cursors and the fetch statement, see the Programming Guide.

You can use the optional order by clause to sort rows alphabetically or numerically, in ascending or descending order. The description of the order by clause that follows provides the details about using this clause.

The begin and end keywords define a statement block. Include this statement block when you want to process the rows with a select loop. OpenROAD performs the operations defined in the statementlist on the values in each row returned by the select statement as the row is returned. Do not use this block if the select statement is part of an open cursor or insert statement. You may use { } instead of begin and end.

If you are selecting into field variables and the select statement returns no rows, OpenROAD does not clear the fields (simple fields or table fields). The fields retain whatever values they displayed before the select statement was executed.

This statement has the following parameters:

The union clause combines the results of a number of select statements. The general syntax is:

You can enclose a subselect in parentheses to make it easier to read unioned select statements.

OpenROAD uses the column names in the first subselect's column list to build the correspondence to the frame variables. Consequently, in the column list for the second and subsequent selects, you cannot use the following formats:

You can join any number of subselects with the union clause. However, all the rows returned by a unioned select statement must be identical to each other in type. For example, the following select statement selects the names and numbers of people from different organizations:

The previous statement is valid because each subselect is returning an employee name and number. You could not union a select that returns an employee name and number with a select statement that returns, for example, an employee name and address because the two select statements are not returning columns of the same type and format.

You can combine data from two or more tables to produce an intermediate results table using an outer join.

Outer joins specified in the from clause are not the same as joins specified in the where clause: the from clause specifies sources of data, while the where clause specifies restrictions to be applied to the sources of data to produce the results table.

Outer joins are specified in the from clause using the following syntax:

You may think of an outer join is as the union of two select statements: the first query returns rows that fulfill the join condition, and the second returns nulls for rows that do not.

There are three types of outer joins:

Right and left joins are symmetrical: (table1 right-join table2) returns the same results as (table2 left-join table1).

By default, joins are evaluated left to right. To override the default order of evaluation, use parentheses.

A source can itself be an outer join, and you can join the results of joins with the results of other joins, as illustrated in the following pseudo code:

How you place restrictions is important in obtaining correct results. For example:

does not return the same results as:

In the first example, the restriction determines which rows in the join result table will be assigned null values; in the second example, the restriction determines which rows will be omitted from the result table.

The following example uses an outer join in the from clause to display all employees with the name of their department, if any:

The where clause tells the database what rows you want to retrieve. The value of searchcondition is a logical (Boolean) database expression that defines some criteria for row selection

When you include this clause, the select statement returns only those rows that fit the criteria defined in the search condition. If you do not include a where clause, the select statement returns as many rows as there are rows in the table. (If the select statement unions two or more selects, then it returns all the rows from the table in each select. Similarly, if a select joins two or more tables, then it returns a Cartesian product if there is no where clause.) The where clause lets you specify which rows you want returned by the select statement.

The syntax of the where clause is:

For example:

This statement selects into the frame variables the name and job title of the first employee returned whose salary is equal to or greater than the value entered in the sallimit field.

You can use simple variables in place of constants in the search condition. For example:

You can also place the entire search condition in a varchar variable. For example:

To build up a search condition from the contents of existing varchar and numeric variables, you could do something like this:

If you place the entire search condition in a varchar variable, you cannot use the repeated keyword in the select statement.

The order by clause lets you specify how the returned rows are ordered. Its syntax is:

where orderspecification is:

You can use either the names or numbers to specify the order, but not both. Additionally, if you use a dynamic name to specify either name or sortorder, you cannot use the repeated option in the select statement.

The name must be taken from a simple_variable, resultname, or dbexpression (if it specifies a database column and simple_variable and resultname are omitted) in the select's target list. If the name in the select target list is expressed with dot notation, use the last component of that notation as the name in the order by clause. For example:

The number is the ordinal position of the name in the select target list. For example, consider the following select statement:

In this statement, e.name is in ordinal position one in the target list and e.dept is in ordinal position two. In the order by clause, we are directing the DBMS to order the returned rows by the variable name in position two, that is, e.dept (the department) and then by the variable name in position one, e.name.

If you include more than one name or number, the DBMS orders the rows on the basis of the first item, the second item, and so on. For example, assume that your application has the following select statement:

If the returned rows are displayed in a table field, the user sees them sorted in this manner:

The rows are sorted first by department, then within each department by name, and within each name (if two are the same) by salary.

The sortorder determines how each item is sorted within itself. You can specify an ascending (asc) or descending (desc) sort order for each item. If you do not specify sortorder, the default is ascending.

If you are sorting by names that are the same, you must use numbers. For example, numbers must be used in the following order by clause:

A select loop is one or more operations that the program performs on each row of values returned by a select statement. Because select loops provide better performance than cursors, you should use select loops whenever the operations that you want to perform on the returned rows are completed within a single event block.

The begin and end keywords define the statement block that is the select loop. You can use any legal 4GL statement in the statement list.

The system variable IIrowcount has an undefined value during the execution of a select loop unless you execute a query statement as part of the loop. In such cases, the value of IIrowcount is defined for that statement. After the select loop completes, the value in IIrowcount reflects the number of rows processed by the loop.

For more information about using select loops and cursors, see the Programming Guide.

Select information about an employee based on the value in the empnum field. Then use the commit statement to end the transaction:

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:

Read into the emptable array all projects for the employee whose number is currently displayed in the empnum field, then use the commit statement to end the transaction:

Retrieve the name and salary and set a variable to indicate the status of the salary: