SELECT (SQL)

#### Search Results

Search

# SELECT (SQL)

This document covers the following topics:

- Function

- Syntax 1 - Cursor-Oriented Selection

- Syntax 2 - Non-Cursor Selection

- Syntax Element Description

- Join Queries

For explanations of the symbols used in the syntax diagrams, see Syntax Symbols.

Belongs to Function Group: Database Access and Update

## Function

The `SELECT` statement supports both the cursor-oriented selection that is used to retrieve an arbitrary number of rows and the non-cursor selection (singleton `SELECT`) that retrieves at most one single row. With the `SELECT ... END-SELECT` construction, Natural uses the same database loop processing as with the `FIND` statement.

Two different structures are possible.

## Syntax 1 - Cursor-Oriented Selection

Like the Natural `FIND` statement, the cursor-oriented `SELECT` statement is used to select a set of rows (records) from one or more Db2 tables, based on a search criterion. Since a database loop is initiated, the loop must be closed by a `LOOP` (reporting mode) or `END-SELECT` statement. With this construction, Natural uses the same loop processing as with the `FIND` statement.

In addition, no cursor management is required from the application program; it is automatically handled by Natural.

- Syntax 1 - Common Set

- Syntax 1 - Extended Set

### Syntax 1 - Common Set

`SELECT` selection into-clause table-expression
|  | `UNION` `EXCEPT` `INTERSECT` |  |  | `ALL` |  |  | `(SELECT` selection table-expression`)` `SELECT` selection table-expression |  |  |
`[ORDER BY` criteria`]`
statement
| `END-SELECT` `LOOP` |  |

### Syntax 1 - Extended Set

`SELECT` selection into-clause table-expression
|  | `UNION` `EXCEPT` `INTERSECT` |  |  | `ALL` |  |  | `(SELECT` selection table-expression`)` `SELECT` selection table-expression |  |  |
`[ORDER BY` criteria`]`
`[OPTIMIZE FOR` integer `ROWS]`
`[WITH` isolation-level`]`
`[FETCH FIRST` row-limit`]`
`[WITH` scroll-mode`]`
`[IF NO RECORDS FOUND` instruction`]`
statement
| `END-SELECT` `LOOP` |  |

## Syntax 2 - Non-Cursor Selection

The `SELECT SINGLE` statement supports the functionality of a non-cursor selection (singleton `SELECT`); that is, a select expression that retrieves at most one row without using a cursor. It cannot be referenced by a positioned `UPDATE` or a positioned `DELETE` statement.

- Syntax 2 - Common Set

- Syntax 2 - Extended Set

### Syntax 2 - Common Set

`SELECT SINGLE`
selection into-clause table-expression
`[``IF NO RECORDS FOUND` instruction`]`
statement
| `END-SELECT` `LOOP` |  |  |  |  |  |

### Syntax 2 - Extended Set

`SELECT SINGLE`
selection into-clause table-expression
`[``WITH` isolation-level`]`
`[``FETCH FIRST` row-limit`]`
`[``IF NO RECORDS FOUND` instruction`]`
statement
| `END-SELECT` `LOOP` |  |  |  |  |  |

## Syntax Element Description

This section alphabetically lists and explains the syntax items contained in the syntax diagrams of Syntax 1 - Cursor-Oriented Selection and Syntax 2 - Non-Cursor Selection:

- END-SELECT | LOOP

- FETCH FIRST row-limit

- IF NO RECORDS FOUND instruction

- into-clause

- OPTIMIZE FOR integer ROWS

- ORDER BY criteria

- selection

- statement

- table-expression

- UNION | EXCEPT | INTERSECT Clause

- WITH isolation-level

- WITH scroll-mode

### END-SELECT | LOOP

In structured mode, the Natural reserved keyword `END-SELECT` must be used to end the `SELECT` statement.

In reporting mode, the `LOOP` statement must be used to end the `SELECT` statement.

### FETCH FIRST row-limit

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

The `FETCH FIRST` clause limits the number of rows to be fetched. It improves the performance of queries with potentially large result sets if only a limited number of rows is needed.

This clause is only valid against Db2 databases. When used against other databases, it will cause runtime errors.

### IF NO RECORDS FOUND instruction

Note
This clause actually does not belong to Natural SQL; it represents Natural functionality which has been made available to SQL loop processing.

#### Structured Mode Syntax

`IF NO` [`RECORDS`] [`FOUND`] |
| `ENTER` |
statement
`END-NOREC`

#### Reporting Mode Syntax

`IF NO` [`RECORDS`] [`FOUND`]
| `ENTER` |
statement
`DO` statement `DOEND`

The `IF NO RECORDS FOUND` clause is used to initiate a processing loop if no records meet the selection criteria specified in the preceding `SELECT` statement.

If no records meet the specified selection criteria, the `IF NO RECORDS FOUND` clause causes the processing loop to be executed once with an "empty" record. If this is not desired, specify the statement `ESCAPE BOTTOM` within the `IF NO RECORDS FOUND` clause.

If one or more statements are specified with the `IF NO RECORDS FOUND` clause, the statements are executed immediately before the processing loop is entered. If no statements are to be executed before entering the loop, the keyword `ENTER` must be used.

Note
If the result set of the `SELECT` statement consists of a single row of `NULL` values, the `IF NO RECORDS FOUND` clause is not executed. This could occur if the selection list consists solely of one of the aggregate functions `SUM`, `AVG`, `MIN` or `MAX` on columns, and the set on which these aggregate functions operate is empty. When you use these aggregate functions in the above-mentioned way, you should therefore check the values of the corresponding null-indicator fields instead of using an `IF NO RECORDS FOUND` clause.

> Database Values

>   Unless other value assignments are made in the statements accompanying an `IF NO RECORDS FOUND` clause, Natural resets to empty all database fields which reference the file specified in the current loop.

> Evaluation of System Functions

>   Natural system functions are evaluated once for the empty record that is created for processing as a result of the `IF NO RECORDS FOUND` clause.

### into-clause

`INTO` |  | parameter, `VIEW` {view-name [correlation-name ]}, |

The `INTO` keyword introduces an `INTO` clause. This clause is used to specify the target fields in the program which are to be filled with the result of the selection.

The `INTO` clause can specify either single `parameters` or one or more views as defined in the `DEFINE DATA` statement.

All target field values can come either from a single table or from more than one table as a result of a join operation (see also Join Queries).

Note
In standard SQL syntax, an `INTO` clause is only used in non-cursor select operations (singleton `SELECT`) and can be specified only if a single row is to be selected. In Natural, however, the `INTO` clause is used for both cursor-oriented and non-cursor select operations.

The `selection` can also merely consist of an asterisk (*). In a standard select expression, this is a shorthand for a list of all column names in the table(s) specified in the `FROM` clause. In the Natural `SELECT` statement, however, the same syntactical item `SELECT *` has a different semantic meaning: all the items listed in the `INTO` clause are also used in the selection. Their names must correspond to names of existing database columns.

Syntax Element Description:

Syntax Element | Description
--- | ---
`parameter` | If single parameters are specified as target fields, their number and formats must correspond to the number and formats of the `columns` and/or `scalar-expressions` specified in the corresponding selection as described above (for details, see Scalar Expressions). See Example 5.
`view-name` | The name a Natural view as defined in the `DEFINE DATA` statement. If one or more views are referenced in the `INTO` clause, the number of items specified in the selection must correspond to the number of fields defined in the view(s) (not counting group fields, redefining fields and indicator fields). Note Both the Natural target fields and the table columns must be defined in a Natural DDM. Their names, however, can be different, since assignment is made according to their sequence. See Example 5.
`correlation-name` | If the `VIEW` clause is used within a `SELECT *` construction where multiple tables are to be joined, `correlation-names` are required if the specified view contains fields that reference columns which exist in more than one of these tables. In order to know which column to select, all these columns are qualified by the specified `correlation-name` at generation of the selection list. The `correlation-name` assigned to a view must correspond to one of the `correlation-names` used to qualify the tables to be joined. See also the section Join Queries and Example 6.

> Examples

>   Example 1:

>       DEFINE DATA LOCAL
>       01 PERS VIEW OF SQL-PERSONNEL
>         02 NAME
>         02 AGE
>       END-DEFINE
>       ...
>       SELECT *
>         INTO NAME, AGE

>   Example 2:

>       ...
>       SELECT *
>         INTO VIEW PERS

>   These examples are equivalent to the following ones:

>   Example 3:

>       ...
>       SELECT NAME, AGE
>         INTO NAME, AGE

>   Example 4:

>       ...
>       SELECT NAME, AGE
>         INTO VIEW PERS

>   Example 5:

>       DEFINE DATA LOCAL
>       01 PERS VIEW OF SQL-PERSONNEL
>         02 NAME
>         02 AGE
>       END-DEFINE
>       ...
>       SELECT FIRSTNAME, AGE
>         INTO VIEW PERS
>         FROM SQL-PERSONNEL
>       ...

>   The target fields `NAME` and `AGE`, which are part of a Natural view, receive the contents of the table columns `FIRSTNAME` and `AGE`.

>   Example 6:

>       DEFINE DATA LOCAL
>       01 PERS VIEW OF SQL-PERSONNEL
>         02 NAME
>         02 FIRST-NAME
>         02 AGE
>       END-DEFINE
>       ...
>       SELECT *
>         INTO VIEW PERS A
>         FROM SQL-PERSONNEL A, SQL-PERSONNEL B
>       ...

### OPTIMIZE FOR integer ROWS

`OPTIMIZE FOR` integer `ROWS`

This clause is only valid against Db2 databases. When used against other databases, it will cause runtime errors.

The `OPTIMIZE FOR integer ROWS` clause is used to inform Db2 in advance of the number (`integer`) of rows to be retrieved from the result table. Without this clause, Db2 assumes that all rows of the result table are to be retrieved and optimizes accordingly.

This optional clause is useful if you know how many rows are likely to be selected, because optimizing for `integer` rows can improve performance if the number of rows actually selected does not exceed the `integer` value (which can be in the range from `0` to `2147483647`).

> Example

>       SELECT name INTO
>       #name FROM table WHERE AGE = 2 OPTIMIZE FOR 100 ROWS

### ORDER BY criteria

`ORDER BY` |  |  | column-reference |  |  | `ASC` |  |  |
integer | `DESC`

The `ORDER BY` clause arranges the result of a `SELECT` statement in a particular sequence.

Syntax Element Description:

Syntax Element | Description
--- | ---
`column-reference` | Each `ORDER BY` clause must specify a column of the result table. In most `ORDER BY` clauses a column can be identified either by `column-reference` (that is, by an optionally qualified column name) or by column number. In a query involving `UNION`, a column must be identified by column number. See also Column Reference.
`integer` | In a query involving `UNION`, a column must be identified by column number. The column number is the ordinal left-to-right position of a column within the selection, which means it is an integer value. This feature makes it possible to order a result on the basis of a computed column which does not have a name.
`ASC|DESC` | Specifies the sort order: ascending (`ASC`) or descending (`DESC`). `ASC` is the default. See Example 2.

> Examples

>   Example 1:

>       DEFINE DATA LOCAL
>       1 #NAME          (A20)
>       1 #YEARS-TO-WORK (I2)
>       END-DEFINE
>       ...
>       SELECT NAME , 65 - AGE
>         INTO #NAME, #YEARS-TO-WORK
>         FROM SQL-PERSONNEL
>         ORDER BY 2
>         ...

>   Example 2:

>       DEFINE DATA LOCAL
>       1 PERS VIEW OF SQL-PERSONNEL
>       1 NAME
>       1 AGE
>       1 ADDRESS   (1:6)
>       END-DEFINE
>       ...
>       SELECT NAME, AGE, ADDRESS
>         INTO VIEW PERS
>         FROM SQL-PERSONNEL
>         WHERE AGE = 55
>         ORDER BY NAME DESC
>         ...

### selection

See Selection in Select Expressions.

### statement

The Natural statement(s) to be executed in the processing loop.

### table-expression

See table-expression in Select Expressions.

### UNION | EXCEPT | INTERSECT Clause

| `UNION` `EXCEPT` `INTERSECT` |  |  | `DISTINCT` `ALL` |  |  | `(SELECT` selection table-expression`)` `SELECT` selection table-expression |  |

`UNION`, `EXCEPT` and `INTERSECT` introduce a query that involves set operations.

Set operations combine the results of two or more `select-expressions`. The columns specified in the individual `select-expressions` must match in number, type and format.

The `INTO` clause must be specified with the first `select-expression` only.

Syntax Element Description:

Syntax Element | Description
--- | ---
`UNION` | Combines the results of two or more `select-expressions`.
`EXCEPT` | Specifies the difference set of the result sets of two `select-expressions`.
`INTERSECT` | Specifies the intersection of two result sets.
`DISTINCT` | Specifies that the result set does not contain redundant (duplicate) rows. `DISTINCT` is the default setting.
`ALL` | Specifies that the result set contains redundant (duplicate) rows. Redundant duplicate rows are eliminated from the result of a set operation unless the set operation explicitly includes the `ALL` qualifier.

> Example

>       DEFINE DATA LOCAL
>       01 PERS VIEW OF SQL-PERSONNEL
>         02 NAME
>         02 AGE
>         02 ADDRESS (1:6)
>       END-DEFINE
>       ...
>       SELECT NAME, AGE, ADDRESS
>         INTO VIEW PERS
>         FROM SQL-PERSONNEL
>         WHERE AGE > 55
>       UNION ALL
>       SELECT NAME, AGE, ADDRESS
>         FROM SQL-EMPLOYEES
>         WHERE PERSNR < 100
>       ORDER BY NAME
>       ...
>       END-SELECT
>       ...

### WITH isolation-level

`WITH` |  | `CS` `RR` `RR KEEP UPDATE LOCK` `RS` `RS KEEP UPDATE LOCKS` `UR` |

This clause allows you to specify an explicit isolation level with which the statement is to be executed.

This clause is only valid against Db2 databases. When used against other databases, it will cause runtime errors.

The following options are provided:

Option | Meaning
--- | ---
`CS` | Cursor Stability
`RR` | Repeatable Read
`RR KEEP UPDATE LOCKS` | Only applies to Syntax 1 - Extended Set and only if a positioned `UPDATE` or a positioned `DELETE` statement is processed with the `SELECT` statement. Repeatable Read and retaining update locks.
`RS` | Read Stability
`RS KEEP UPDATE LOCKS` | Only applies to Syntax 1 - Extended Set and only if a positioned `UPDATE` or a positioned `DELETE` statement is processed with the `SELECT` statement. Read Stability and retaining update locks.
`UR` | Uncommitted Read `UR` can only be specified within a `SELECT` statement and when the table is read-only. The default isolation level is determined by the isolation of the package or plan into which the statement is bound. The default isolation level also depends on whether the result table is read-only or not. To find out the default isolation level, refer to the IBM literature.

### WITH scroll-mode

`WITH` |  | `ASENSITIVE SCROLL` `INSENSITIVE SCROLL` `SENSITIVE STATIC SCROLL` `SENSITIVE DYNAMIC SCROLL` |  | [:] scroll_hv [`GIVING` [:] sqlcode]

Natural supports SQL scrollable cursors by using the clauses `WITH ASENSITIVE SCROLL`, `WITH SENSITIVE STATIC SCROLL`, and `SENSITIVE DYNAMIC SCROLL`. Scrollable cursors allow Natural applications to position randomly any row in a result set. With non-scrollable cursors, the data can only be read sequentially, from top to bottom.

RDBMS scrollable cursors are enabled with this clause. Scrollable cursors can be `ASENSITIVE`, `INSENSITIVE`, `SENSITIVE STATIC`, or `SENSITIVE DYNAMIC`.

Scrollable cursors allow the application to position any row in the cursor at any time as long as the cursor is open. Scrollable cursors are not supported for Sybase databases at all. Scrollable cursors are not supported for the MS SQL Server DBLIB interface, but only for the MS SQL Server ODBC interface.

The positioning is performed depending on the content of the `scroll_hv`. The content is evaluated each time a `FETCH` against the database is executed.

Note
Not all SQL database systems support all options.

Syntax Element Description:

Syntax Element | Description
--- | ---
`ASENSITIVE SCROLL` | Specifies that the cursor is either `INSENSITIVE` or `SENSITIVE DYNAMIC`. This is determined by the database at open time of the cursor, depending on the read-only property of the cursor: If the cursor is read-only, the cursor will become `INSENSITIVE`. If the cursor is not read-only, the cursor will become `SENSITIVE DYNAMIC`. This is supported for Db2 databases.
`INSENSITIVE SCROLL` | Specifies that the cursor is insensitive for updates, deletes and inserts executed against the base table, after the cursor has been updated.`INSENSITIVE SCROLL` refers to a cursor that cannot be used in Positioned `UPDATE` or Positioned `DELETE` operations. This is supported for Oracle, Adabas D, MS SQL Server ODBC, MySQL, MariaDB, PostgreSQL, and Db2 databases. In addition, once opened, an `INSENSITIVE SCROLL` cursor does not reflect `UPDATE`, `DELETE` or `INSERT` operations against the base table after the cursor was opened. See also Note.
`SENSITIVE STATIC SCROLL` | Specifies that the cursor is sensitive for updates and deletes against the base table, but not against inserts, after the cursor has been opened.`SENSITIVE STATIC SCROLL` refers to a cursor that can be used for Positioned `UPDATE` or Positioned `DELETE` operations. This is supported for Adabas D, MS SQL Server ODBC and Db2 databases.In addition, a `SENSITIVE STATIC SCROLL` cursor reflects `UPDATE` and `DELETE` operations of base table rows. The cursor does not reflect `INSERT` operations. See also Note.
`SENSITIVE DYNAMIC SCROLL` | `SENSITIVE DYNAMIC` specifies that the cursor is sensitive for updates, deletes and inserts against the base table, after the cursor has been opened. `SENSITIVE DYNAMIC` scrollable cursors reflect `UPDATE` and `DELETE` operations against the base table while the cursor is open. This is supported for Adabas D, MS SQL Server ODBC and Db2 databases.

Note:
`INSENSITIVE` and `SENSITIVE STATIC` scrollable cursors use temporary result tables and require a `TEMP` database in Db2 (see the relevant Db2 literature by IBM).

> scroll_hv

>   The variable `scroll_hv` must be alphanumeric.

>   The variable `scroll_hv` specifies which row of the result table will be fetched during one execution of the database processing loop. The content of `scroll_hv` is evaluated each time the database processing loop cycle is executed.

>   | `INSENSITIVE` `SENSITVE` |  |  | `CURRENT` `FIRST` `LAST` `PRIOR` `NEXT` |
>   | `ABSOLUTE` `RELATIVE` |  |  | `+` `-` |  | integer

> scroll_hv Options

>   Option | Explanation
>   --- | ---
>   `CURRENT` | Fetches the current row (again).
>   `FIRST` | Fetches the first row.
>   `LAST` | Fetches the last row.
>   `NEXT` | Fetches the row after the current one. This is the default value.
>   `PRIOR` | Fetch the row before the current one.
>   `+|-integer` | Only applies in connection with `ABSOLUTE` or `RELATIVE`. Specifies the position of the row to be fetched `ABSOLUTE` or `RELATIVE`. Enter a plus (+) or minus (-) sign followed by an integer. The default value is a plus (+).
>   `ABSOLUTE` | Only applies in connection with `+|-integer`. Uses `integer` as the absolute position within the result set from where the row is fetched.
>   `RELATIVE` | Only applies in connection with `+|-integer`. Uses `integer` as the relative position to the current position within the result set from where the row is fetched.

>   There are some restrictions for special RDBMS systems:

>   - Db2 does not support the keyword `CURRENT`.

>   - In a `SELECT FOR UPDATE` loop Db2 only supports `NEXT` as scrolling option.

>   - MS SQL Server (ODBC interface) does not support the keyword `CURRENT`.

>   - Adabas D does not support `RELATIVE` scrolling.

> GIVING [:] sqlcode

>   The specification of `GIVING [:] sqlcode` is optional. If specified, the Natural variable `[:] sqlcode` must be of format I4. The values for this variable are returned from the Db2 `SQLCODE` of the underlying `FETCH` operation. This allows the application to react to different statuses encountered while the scrollable cursor is open. The most important status codes indicated by `SQLCODE` are listed in the following table:

>   SQLCODE | Explanation
>   --- | ---
>   `0` | `FETCH` operation successful, data returned except for `FETCH` with option `BEFORE` or `AFTER`.
>   `+100` | Row not found, cursor still open, no data returned.
>   `-1` | General error while trying to `FETCH` a row

>   If you specify `GIVING [:] sqlcode`, the application must react to the different statuses. If an `SQLCODE +100` is entered five times successively and without terminal I/O, the Natural for Db2 runtime will issue Natural error NAT3296 in order to avoid application looping. The application can terminate the processing loop by executing an `ESCAPE` statement.

>   If you do not specify `GIVING [:] sqlcode`, except for `SQLCODE 0` and `SQLCODE +100`, each `SQLCODE` will generate Natural error NAT3700 and the processing loop will be terminated. `SQLCODE +100` (row not found) will terminate the processing loop.

>   See also the example program `DEM2SCRL` supplied in the Natural system library `SYSDB2`.

## Join Queries

A join is a query in which data is retrieved from more than one table. All the tables involved must be specified in the `FROM` clause.

A join always forms the Cartesian product of the tables listed in the `FROM` clause and later eliminates from this Cartesian product table all the rows that do not satisfy the join condition specified in the `WHERE` clause.

Correlation names can be used to save writing if table names are rather long. Correlation names must be used when a column specified in the selection list exists in more than one of the tables to be joined in order to know which of the identically named columns to select.

### Example

    DEFINE DATA LOCAL
    1 #NAME     (A20)
    1 #MONEY    (I4)
    END-DEFINE
    ...
    SELECT NAME, ACCOUNT
      INTO #NAME, #MONEY
      FROM  SQL-PERSONNEL P, SQL-FINANCE F
      WHERE P.PERSNR = F.PERSNR
        AND F.ACCOUNT > 10000
        ...

### On this page

- Function
- Syntax 1 - Cursor-Oriented Selection
- Syntax 2 - Non-Cursor Selection
- Syntax Element Description
- Join Queries