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

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

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

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

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

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.

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

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

FETCH FIRST Clause

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