Select Statement
This statement retrieves rows from a database table.
This statement has the following syntax:
[repeated] subselect
{union [all] subselect}
[order by orderclause
[beginendblock];
where subselect has the following syntax:
select [all | distinct]
resultexpression{, resultexpression}
from fromclause
[where searchcondition]
[group by groupclause]
[having searchcondition]
and fromclause has the following alternative syntaxes:
from tablename [corrname]{, tablename [corrname]}
from :fromvariable
and groupclause has the following alternative syntaxes:
[group by columnname{, columnname}]
[group by :groupvariable
and orderclause has the following alternative syntaxes:
[order by orderspecification{, orderspecification}]
[order by :ordervariable
You can use the select statement in two ways:
• To retrieve one row of data (called a "singleton select")
• To retrieve multiple rows into a result table
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:
simple_variable = dbexpression
or
dbexpression as simple_variable
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:
resultname = dbexpression
dbexpression as resultname
dbexpression
*
corrname.*
Note: The asterisk selects values from all columns.
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.