Select Expressions

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:

SELECT NAME, 65 - AGE 
  FROM SQL-PERSONNEL 
  ...

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

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

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

Table Reference

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.

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

Correlation Clause

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

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:

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.

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:

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

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

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

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.

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