SQL Statements › SELECT › Subselect › SELECT Clause

SELECT Clause

SELECT

Produces a final result table by selecting only the columns indicated by the select list from R, where R is the result of the previous clause.

Note: We do not impose an arbitrary limit on the number of columns you can select in a query. We are limited only by environmental factors. Some of these factors are the size of your work area as specified by the size parameter of the TASKS Multi-User startup option (see the CA Datacom/DB Database and System Administration Guide), your column sizes, and limits placed by other products such as the CA Datacom Server.

ALL

Retains all rows of the final result table and does not eliminate redundant duplicates. ALL is the default.

DISTINCT

Eliminates all but one of each set of duplicate rows of the final result table.

DISTINCT must not be used more than once in a subselect. This restriction includes:

1. Functions in the SELECT list of the subselect.
2. Functions in a HAVING clause of a subselect.
3. Functions which are specified in a subquery of the HAVING clause and contain a correlated reference to groups of the subselect.

*

The asterisk (*) represents a list of names that identify the columns of R. The first name in the list identifies the first column of R, the second name identifies the second column of R, and so on. The list of names is established when the program containing the SELECT clause is prepared.

Important! SELECT * and SELECT table.* (see the following) are useful when selecting from tables or views in an interactive environment, especially when the names of the columns are not known. However, embedding SELECT * or SELECT table.* in an application program may cause unexpected results if the definition of the table is ever altered. For example, when a column is dropped from a table definition, all statements which reference the table are automatically rebound as they are executed. The rebound forms of the statements reflect the new set of columns. The application program, however, still expects the original set of columns, and the result table returned to the program no longer matches the FETCH statement's host variables. Views which are referenced by application programs should not include SELECT * or SELECT table.* for the same reason.

name.*

The asterisk (*) represents a list of names that identify the columns of R. The name can be a table-name, view-name, or correlation-name, and must designate a table-name or view-name in the FROM clause. The first name in the list identifies the first column of R, the second name identifies the second column, and so on. The list is established at preparation time and does not represent any columns that have been added later. The names must be separated by commas.

expression

Commonly, the expressions used in a SELECT statement include column names. Each column name used in the select list must unambiguously identify a column of R. Multiple expressions must be separated by commas.

The number of columns in the result of SELECT is the same as the number of expressions in the operational form of the select list, that is to say, the list established at preparation time.

The result of a subquery must be a single column unless the subquery is used in the EXISTS predicate.