Select Expressions

#### Search Results

Search

# Select Expressions

`SELECT` selection table-expression

A `select-expression` specifies a result table. It is used in the following Natural SQL statements: `INSERT` | `SELECT` | `UPDATE`

This document covers the following topics:

- Selection

- Table Expression

## Selection

| `ALL` `DISTINCT` |  |  |  | scalar-expression [[`AS`] correlation-name] unpack-row |  | , |
| `*` |

A `selection` specifies the columns of the result set tables to be selected.

Syntax Element Description:

Syntax Element | Description
--- | ---
`ALL|DISTINCT` | Elimination of Duplicate Rows: Duplicate rows are not automatically eliminated from the result of a `select-expression`. To request this, specify the keyword `DISTINCT`. The alternative to `DISTINCT` is `ALL`. `ALL` is assumed if neither is specified.
`scalar-expression` | Scalar Expression: Instead of, or as well as, simple column names, a selection can also include general scalar expressions containing scalar operators and scalar functions which provide computed values (see also the section Scalar Expressions). Example:
    SELECT NAME, 65 - AGE
      FROM SQL-PERSONNEL
      ...
`AS` | The optional keyword `AS` introduces a `correlation-name` for a column.
`correlation-name` | Correlation Name: A `correlation-name` can be assigned to a `scalar-expression` as an alias name for a result column. The `correlation-name` need not be unique. If no `correlation-name` is specified for a result column, the corresponding `column-name` will be used (if the result column is derived from a column name; if not, the result table will have no name). The name of a result column may be used, for example, as column name in the `ORDER BY` clause of a `SELECT` statement.
`unpack-row` | See unpack-row below.
`*` | Asterisk Notation: All columns of the result table are selected. Example:
    SELECT *
      FROM SQL-PERSONNEL, SQL-AUTOMOBILES
      ...

#### unpack-row

`UNPACK` (scalar-expression) `.* AS` ({field-name data-type} , )

An `unpack-row` specifies a row of unpacked binary values that are returned when the SQL UNPACK function is invoked. The number of field-names and data-types must match the number of fields returned by the UNPACK function.

## Table Expression

from-clause [where-clause]
[group-by-clause] [having-clause]
[order-by-clause] [fetch-first-clause]

The `table-expression` specifies from where and according to what criteria rows are to be selected.

The following topics are covered below:

- FROM Clause

- Table Reference

- WHERE Clause

- GROUP BY Clause

- HAVING Clause

- ORDER BY Clause

- INPUT SEQUENCE

- ORDER OF table-designator

- FETCH FIRST Clause

- Examples of Table Expressions

### FROM Clause

`FROM` table-reference,…

This clause specifies from which tables the result set is built.

### Table Reference

| table-name [period-specification] [correlation-clause] [`TABLE`] subquery correlation-clause joined-table `TABLE` (function-name (scalar-expression,...)) correlation-clause data-change-table-reference [correlation-clause] xmltable-function correlation-clause |

The tables specified in the `FROM` clause must contain the column fields used in the selection list.

You can either specify a single table or produce an intermediate table resulting from a subquery or a "join" operation (see below).

Since various tables (that is, DDMs) can be addressed in one `FROM` clause and since a `table-expression` can contain several `FROM` clauses if `subqueries` are specified, the database ID (DBID) of the first DDM specified in the first `FROM` clause of the whole expression is used to identify the underlying database involved.

#### TABLE function-name Clause

The `TABLE function-name` clause belongs to the SQL Extended Set and requires a `correlation-clause` with a `column-name` list.

#### Period Specification

The `period-specification` clause belongs to the SQL Extended Set.

`FOR` |  | `SYSTEM_TIME` `BUSINESS_TIME` |  |  | `AS OF` expr `FROM` expr1 `TO` expr2 `BETWEEN` expr1 `AND` expr2 |  |

`period-specification` optionally specifies that a period specification applies to the temporal table `table-name`. The same period name (`SYSTEM_TIME` or `BUSINESS_TIME`) must not be specified more than one time for the same table.

Syntax Element | Description
--- | ---
`FOR SYSTEM_TIME` | Specifies that the `SYSTEM_TIME` period is used for the period-specification. `SYSTEM_TIME` must be a period that is defined in the table and the table must be a system-maintained temporal table that is defined with system data versioning.
`FOR BUSINESS_TIME` | Specifies that the `BUSINESS_TIME` period is used for the `period-specification`. `BUSINESS_TIME` must be a period that is defined in the table.
`expr`, `expr1`, `expr2` | Specify expressions that return a value of a built-in data type that is comparable to the data type of the columns of the specified period and must not contain a `TIME ZONE`.
`AS OF expr` | Specifies that the table includes each row for which the start value for the specified period is less than or equal to `expr` and the end value for the period is greater than `expr`.
`FROM expr1 TO expr2` | Specifies that the table includes rows that exist for the period specified from `expr1` up to `expr2`. A row is included in the table if the start value for the period in the row is less than `expr2` and the end value for the period in the row is greater than `expr1`.
`BETWEEN expr1 AND expr2` | Specifies that the table includes a row in which the specified period overlaps at any point in time between `expr1` and `expr2`. A row is included in the table if the start value for the period in the row is less than or equal to `expr2` and the end value for the period in the row is greater than `expr1`.

Optionally, a `correlation-clause` can be assigned to a `table-name`. For a `subquery`, a `correlation-clause` must be assigned.

#### Correlation Clause

[`AS`] correlation-name [(column-name,...)]

A `correlation-clause` consists of optional keyword `AS` and a `correlation-name` and is optionally followed by a plain `column-name` list. The `column-name` list belongs to the SQL Extended Set.

#### Joined Table

| table-reference |  |  | `INNER` `LEFT [OUTER]` `RIGHT [OUTER]` `FULL [OUTER]` |  |  | `JOIN` table-reference `ON` join-condition |
(joined-table)

A `joined-table` specifies an intermediate table resulting from a "join" operation.

The "join" can be an `INNER`, `LEFT OUTER`, `RIGHT OUTER` or `FULL OUTER` `JOIN`. If you do not specify anything, `INNER` applies.

Multiple "join" operations can be nested; that is, the tables which create the intermediate result table can themselves be intermediate result tables of a "join" operation or a `subquery`; and the latter, in turn, can also have a `joined-table` or another `subquery` in its `FROM` clause.

#### Join Condition

For `INNER`, `LEFT OUTER`, and `RIGHT OUTER` joins:

`search-condition`

For `FULL OUTER` joins:

full-join-expression = full-join-expression [`AND` ]

#### Full Join Expression

| column-name |
| `VALUE` `COALESCE` |  | (column-name `,` )

Within a `join-expression` only `column-names` and the `scalar-function` `VALUE` (or its synonym `COALESCE`) are allowed.

See details on `column-name`.

#### Data Change Table Reference

The `data-change-table-reference` clause belongs to the SQL Extended Set.

| `FINAL TABLE` (INSERT-statement) |
| `FINAL` `OLD` |  | `TABLE` (searched-UPDATE-statement)
`OLD TABLE` (searched-DELETE-statement)
`FINAL TABLE` (MERGE-statement)

A `data-change-table-reference` specifies an intermediate result table, which is based on the rows that are changed by the SQL change statement specified in the clause. A `data-change-table-reference` can only be specified as the only table reference in the `FROM` clause.

Syntax Element Description:

Syntax Element | Description
--- | ---
`FINAL TABLE` | Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement as they appear at the completion of the SQL data change statement.
`OLD TABLE` | Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement as they exist prior to the application of the SQL data change statement.

#### XMLTABLE Function

The `xmltable-function` clause belongs to the SQL Extended Set.

The item `xmltable-function` specifies an invocation of the built-in `XMLTABLE` function.

### WHERE Clause

[`WHERE` search-condition]

The `WHERE` clause is used to specify the selection criteria (`search-condition`) for the rows to be selected.

Example:

    DEFINE DATA LOCAL
    01 NAME   (A20)
    01 AGE    (I2)
    END-DEFINE
    ...
    SELECT *
      INTO NAME, AGE
      FROM SQL-PERSONNEL
      WHERE AGE = 32
    END-SELECT
    ...

For further information, see Search Conditions.

### GROUP BY Clause

| `GROUP BY` |  | grouping-expression grouping-set super-group |  | , |

The `GROUP BY` clause specifies a grouping of the result table. The result of `GROUP BY` is a set of groups of rows. Within each group of more than one row, all values defining the group are equal.

> grouping-expression

>   A grouping expression is a scalar expression that defines the grouping of a result set.

> grouping-set

>   `GROUPING SETS` | ( |  |  |  | grouping-expression super-group |  |  |  |  | , | )
>   ( |  | grouping-expression super-group |  | , | )

>   A `grouping-set` is used to specify multiple grouping clauses in a single statement. A `grouping-set` combines two or more groups of rows into a single result set. It is the same as the union of multiple select expressions with a `GROUP BY` clause where each expression corresponds to one `grouping-set`. A `grouping-set` is a single element or a list of elements delimited by parentheses. An element is either a `grouping–expression` or a `super-group`. A `grouping-set` has the advantage that the groups are computed with a single pass over the base table.

> super-group

>   | `ROLLUP` `CUBE` | (grouping-expression-list) (grouping-expression-list |
>   | `( )`

>   A `super-group` is a more complex `grouping-set`.

>   A `grouping-expression-list` defines the number of elements used in a `ROLLUP` or `CUBE` operation. Elements with multiple `grouping-expressions` are delimited by parentheses:

>   | grouping-expression |  | ,
>   (grouping-expression, )

>   Grand total `( )`:

>   `ROLLUP` and `CUBE` return a row which is the overall (grand total) aggregation. This can be specified with empty parentheses `( )` within the `GROUPING SETS` clause.

>   > ROLLUP

>   >   A `ROLLUP` grouping is like a series of `grouping-sets`. In addition to the regular grouped rows, a `ROLLUP` grouping produces a result set that contains subtotal rows. Subtotal rows are "super-aggregate" rows which contain additional aggregates. The aggregate values are retrieved with the same column functions that are used to obtain the regular grouped rows.

>   >   In general, you specify a `ROLLUP` with `n` elements as

>   >       GROUP BY ROLLUP (c1, c2, ..., cn-1, cn)

>   >   which is the equivalent of:

>   >       GROUP BY GROUPING SETS ((c1, c2, ..., cn-1, cn),

>   >                              (c1, c2, ..., cn-1),

>   >                              ...

>   >                              (c1, c2),

>   >                              (c1),

>   >                              ( ))

>   > CUBE

>   >   A `CUBE` grouping is like a series of `grouping-sets`. In addition to the `ROLLUP` aggregation rows, `CUBE` produces a result set that contains cross-tabulation rows. Cross-tabulation rows are additional "super-aggregate" rows. The `grouping-expression-list` of a `CUBE` computes all permutations along with the grand total. As a result, the `n` elements of a `CUBE` translate to 2**n `grouping-sets`. For example:

>   >       GROUP BY CUBE (a, b, c)

>   >   is the equivalent of:

>   >       GROUP BY GROUPING SETS ((a, b, c),

>   >                              (a, b),

>   >                              (a, c),

>   >                              (b, c),

>   >                              (a),

>   >                              (b),

>   >                              (c),

>   >                              ())

### HAVING Clause

[`HAVING` search-condition]

If the `HAVING` clause is specified, the `GROUP BY` clause should also be specified.

Just as the `WHERE` clause is used to exclude rows from a result table, the `HAVING` clause is used to exclude groups and therefore also based on a `search-condition`. Scalar expressions in a `HAVING` clause must be single-valued per group.

For further information, see Scalar Expressions and Search Conditions.

Example:

    DEFINE DATA LOCAL
    1 #NAME     (A20)
    1 #AVGAGE   (I2)
    1 #NUMBER   (I2)
    END-DEFINE
    ...
    SELECT NAME, AVG(AGE), COUNT(*)
      INTO #NAME, #AVGAGE, #NUMBER
      FROM SQL-PERSONNEL
      GROUP BY NAME
      HAVING COUNT(*) > 1
      ...

### ORDER BY Clause

`ORDER BY` |  | sort-key |  | `ASC` `DESC` |  | , |
`INPUT SEQUENCE`
`ORDER OF` table-designator

The `order-by` clause specifies row ordering of the result table.

#### sort-key

| column-name |
integer
sort-key-expression

A `sort-key-expression` is an expression which is more than a `column-name` or an unsigned `integer` constant.

### INPUT SEQUENCE

`INPUT SEQUENCE` belongs to the SQL Extended Set.

`INPUT SEQUENCE` indicates that the result table reflects the input order of the rows specified in the `VALUES` clause of an `INSERT` statement.

`INPUT SEQUENCE` can only be specified if an `INSERT` statement is specified in the `from-clause`.

### ORDER OF table-designator

`ORDER OF table-designator` belongs to the SQL Extended Set.

`ORDER OF` specifies that the row ordering of the designated table by the `table-designator` is applied to the result table of the query.

`table-designator` uniquely identifies a base table, a view, or the nested table expression of a subselect.

### FETCH FIRST Clause

The `fetch-first` clause belongs to the SQL Extended Set.

| `FETCH FIRST` |  | `1` integer |  |  | `ROWS` `ROW` |  | `ONLY` |

The `fetch-first` clause limits the number of rows that can be fetched. It improves the performance of queries when only a limited number of rows are needed.

### Examples of Table Expressions

Example 1:

    DEFINE DATA LOCAL
    01 #NAME      (A20)
    01 #FIRSTNAME (A15)
    01 #AGE       (I2)
    ...
    END-DEFINE
    ...
    SELECT NAME, FIRSTNAME, AGE
      INTO #NAME, #FIRSTNAME, #AGE
      FROM SQL-PERSONNEL
        WHERE NAME IS NOT NULL
          AND AGE > 20
    ...
      DISPLAY #NAME #FIRSTNAME #AGE
    END-SELECT
    ...
    END

Example 2:

    DEFINE DATA LOCAL
    01 #COUNT    (I4)
    ...
    END-DEFINE
    ...
    SELECT SINGLE COUNT(*) INTO #COUNT FROM SQL-PERSONNEL
    ...

### On this page

- Selection
- Table Expression