
Natural for Mainframes	Version 8.2.6 for Mainframes	SEARCH CONTENTS \| PDF BOOKS \| HOME UP PREV NEXT

— Statements —

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`]		,
					`*`

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.
`*`	Asterisk Notation: All columns of the result table are selected. Example: SELECT * FROM SQL-PERSONNEL, SQL-AUTOMOBILES ...

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
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

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

`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 base 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 item xmltable-function specifies an invocation of the built-in XMLTABLE function.

WHERE Clause

[WHERE search-condition]

The WHERE clause is used a 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 column-reference,

]

The GROUP BY clause rearranges the table represented by the FROM clause into groups in a way that all rows within each group have the same value for the GROUP BY columns.

Each column-reference in the selection list must be either a GROUP BY column or specified within an aggregate-function. Aggregate functions are applied to the individual groups (not to the entire table). The result table contains as many rows as groups.

For further information, see Column Reference and Aggregate Function.

Example:

DEFINE DATA LOCAL 
1 #AGE     (I2) 
1 #NUMBER  (I2) 
END-DEFINE 
... 
SELECT AGE , COUNT(*)  
  INTO #AGE, #NUMBER 
  FROM SQL-PERSONNEL 
  GROUP BY AGE 
  ...

If the GROUP BY clause is preceded by a WHERE clause, all rows that do not satisfy the WHERE clause are excluded before any grouping is done.

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`

`sort-key`

	`column-name`
	`integer`
	`sort-key-expression`

FETCH FIRST Clause

FETCH FIRST

1
integer

ROWS
ROW

ONLY

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   
...

SEARCH CONTENTS | PDF BOOKS | HOME UP PREV NEXT

Select Expressions

Selection

Table Expression

FROM Clause

Table Reference

TABLE function-name Clause

Period Specification

Correlation Clause

Joined Table

Join Condition

Full Join Expression

Data Change Table Reference

XMLTABLE Function

WHERE Clause

GROUP BY Clause

HAVING Clause

ORDER BY Clause

sort-key

FETCH FIRST Clause

Examples of Table Expressions

TABLE `function-name` Clause

`sort-key`