+ -

factor (scalar-expression)

scalar-expression scalar-operator scalar-expression

This document covers the following topics:

• Scalar Expression

• Scalar Operator

• Factor

• Row Value Expression

---

Scalar Expression

A scalar-expression consists of a factor or other scalar expressions including scalar operators.

Concerning reference priority, scalar expressions behave as follows:

• When a non-qualified variable name is specified in a scalar expression, the first approach is to resolve the variable name as column name of the referenced table.

• If no column with the specified name is available in the referenced table, Natural tries to resolve this variable as a Natural user-defined variable (host variable).

Scalar Operator

	+
	-
	*
	/
	| |
	CONCAT

A scalar-operator can be any of the operators listed above. The minus (-) and slash (/) operators must be separated by at least one blank from preceding operators.

Factor

Common Set Syntax:

atom column-reference aggregate-function special-register

Extended Set Syntax:

atom column-reference aggregate-function olap-specification row-change-expression special-register scalar-function (scalar-expression,) labeled-duration case-expression cast-expression user-defined-function-reference sequence-reference time-zone-specific-expression

A factor can consist of one of the items listed in the above diagram and described in the text below.

Atom

parameter constant

 An atom can be either a parameter or a constant.

Column Reference

table-name. correlation-name.

column-name

A column-reference is a column name optionally qualified by either a table-name or a correlation-name (see also the section Basic Syntactical Items). Qualified names are often clearer than unqualified names and sometimes they are essential.

If a column is referenced by a table-name or correlation-name, it must be contained in the corresponding table. If neither a table-name nor a correlation-name is specified, the respective column must be in one of the tables specified in the FROM clause (see Table Expression).

Aggregate Function

Common Set Syntax:

	COUNT				(*) (DISTINCT column-reference)
		AVG MAX MIN SUM			(DISTINCT column-reference) ([ALL] scalar-expression)

Extended Set Syntax:

COUNT COUNT-BIG

(

ALL DISTINCT

scalar-expression

)

*

AVG MAX MIN SUM STDDEV STDDEV_SAMP VARIANCE VARIANCE_SAMP

(

ALL DISTINCT

scalar-expression )

CORRELATION COVARIANCE COVARIANCE_SAMP

(scalar-expression-1,scalar-expression-2)

SQL provides a number of special functions to enhance its basic retrieval power. The so-called SQL aggregate functions currently available and supported by Natural are:

AVG	gives the average of the values in a column
COUNT	gives the number of values in a column
MAX	gives the highest value in a column
MIN	gives the lowest value in a column
SUM	gives the sum of the values in a column

Apart from COUNT(*), each of these functions operates on the collection of scalar values in an argument (that is, a single column or a scalar-expression) and produces a scalar value as its result.

Example:

DEFINE DATA LOCAL 
1 AVGAGE   (I2) 
END-DEFINE 
... 
SELECT AVG (AGE) 
  INTO AVGAGE 
  FROM SQL-PERSONNEL 
  ...

DISTINCT

In general, the argument can optionally be preceded by the keyword DISTINCT to eliminate redundant duplicate values before the function is applied.

If DISTINCT is specified, the argument must be the name of a single column; if DISTINCT is omitted, the argument can consist of a general scalar-expression.

DISTINCT is not allowed with the special function COUNT(*), which is provided to count all rows without eliminating any duplicates.

ROW CHANGE Expression

ROW CHANGE		TIMESTAMP		FOR table-designator
		TOKEN

A ROW CHANGE expression returns a token or a timestamp that represents the last change to a row.

TIMESTAMP	Specifies a timestamp is returned that represents the last time when a row was changed.
TOKEN	Specifies a token of type BIGINT is returned that represents a relative point in the modification sequence of a row.
FOR table-designator	Identifies the table in which the expression is referenced. table-designator has to be a valid Natural SQL DDM.

OLAP Specification

ordered-OLAP-specification numbering-specification aggregation-specification

ordered-OLAP-specification

	RANK		( ) OVER ([window-partition-clause] window-order-clause)
	DENSE_RANK

numbering-specification

ROW_NUMBER ( ) OVER ([window-partition-clause] [window-order-clause])

aggregation-specification

aggregate-function OVER ( [window-partition-clause])
	RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	window-order-clause		RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING window-aggregation-group-clause

aggregate-function

	AVG function
	CORRELATION function
	COUNT function
	COUNT_BIG function
	COVARIANCE function
	MAX function
	MIN function
	STDDEV function
	SUM function
	VARIANCE function

window-aggregation-group-clause

ROWS RANGE

group-start group-between group-end

group-start

UNBOUNDED PRECEDING unsigned-constant PRECEDING CURRENT ROW

group-between

BETWEEN group-bound-1 AND group-bound-2

group-bound-1

UNBOUNDED PRECEDING unsigned-constant PRECEDING unsigned-constant FOLLOWING CURRENT ROW

group-bound-2

UNBOUNDED FOLLOWING unsigned-constant PRECEDING unsigned-constant FOLLOWING CURRENT ROW

group-end

UNBOUNDED FOLLOWING unsigned-constant FOLLOWING

window-partition-clause

PARTITION BY partitioning-expression,...

window-order-clause

ORDER BY {sort-key-expression			ASC			},...
			NULLS LAST
			ASC NULLS FIRST
			DESC
			DESC NULLS FIRST
			DESC NULLS LAST

Online analytical processing (OLAP) specifications provide the ability to return ranking, row numbering and aggregation information as a scalar value in the result of a query. An OLAP specification can be included in an expression, in a select-list, or in the ORDER BY clause of a SELECT statement. The query result to which the OLAP specifications are applied is the result table of the innermost subselect that includes the OLAP specifications.

RANK	Specifies that the rank of a row is defined as 1 plus the number of rows that strictly precede the row.
DENSE_RANK	Specifies that the rank of a row is defined as 1 plus the number of preceding rows that are distinct with respect to the ordering.
ROW_NUMBER	Specifies that a sequential row number is computed for the row that is defined by the ordering, starting with 1 for the first row.
PARTITION BY	Defines the partition within which the OLAP operation is applied.
ORDER BY	Defines the ordering of rows within a partition that is used to determine the value of the OLAP specification.
ASC	Specifies that the values of sort-key-expression are used in ascending order.
DESC	Specifies that the values of sort-key-expression are used in descending order.
NULLS_FIRST	Specifies that the window ordering considers null values before all non-null values in the sort order.
NULLS LAST	Specifies that the window ordering considers null values after all non-null values in the sort order.

Example:

Display the ranking of employees that have a total salary of more than $30,000, in order by last name.

SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
  RANK() OVER(ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
  FROM DSN8910-EMP WHERE SALARY+BONUS > 30000
  ORDER BY LASTNAME;

Time-Zone-Specific Expression

time-zone-specific-expression

Specifies a timestamp with time zone value: AT LOCAL or AT TIME ZONE

AT LOCAL

function-invocation (expression) constant column-name variable special-register scalar-fullselect case-expression cast-specification

{AT LOCAL}

AT TIME ZONE

function-invocation (expression) constant column-name variable special-register scalar-fullselect case-expression cast-specification

{AT TIME ZONE}

function-invocation (expression) constant column-name variable special-register scalar-fullselect case-expression cast-specification

Special Register

special-register

A reference to a special register returns a scalar value.

For more information on the special registers that are supported by Natural, see special-register in the section Syntactical Items Common to Natural SQL Statements in the Database Management System Interfaces documentation.

Scalar Function

scalar-function

A scalar function is a built-in function that can be used in the construction of scalar computational expressions.

For information on the scalar functions that are supported by the Natural, see scalar-function in the section Syntactical Items Common to Natural SQL Statements in the Database Management System Interfaces documentation.

Labeled Duration

labeled-duration

scalar-expression

YEAR YEARS MONTH MONTHS DAY DAYS HOUR HOURS MINUTE MINUTES SECOND SECONDS MICROSECOND MICROSECONDS

A labeled-duration denotes a specific unit of time as expressed by a number which can be an expression followed by one of the duration keywords.

labeled-duration does not conform to standard SQL, and is therefore supported by the Natural SQL Extended Set only.

Case Expression

case-expression

CASE

searched-when-clause simple-when-clause

ELSE

NULL scalar-expression

END

A case-expression does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.

Searched WHEN Clause

WHEN search-condition THEN

NULL scalar-expression

A Searched When Clause does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.

See details on search-condition.

Simple WHEN Clause

scalar-expression

WHEN scalar-expression THEN

NULL scalar-expression

A Simple WHEN Clause does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.

Cast Expression

cast-expression

CAST (scalar-expression AS data-type)

A CAST expression does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.

User-Defined Function Reference

The option user-defined-function-reference belongs to the Natural SQL Extended Set. This options enables you to invoke any user-defined function. Arguments have to be placed in brackets and separated by commas. The user-defined function must be declared in the target RDBMS.

Sequence Reference

The option sequence-reference belongs to the Natural SQL Extended Set.

NEXT VALUE FOR sequence-name PREVIOUS VALUE FOR sequence-name

This option enables you to reference the next value or the previous value of a sequence object. The sequence object has to be created in the target RDBMS before it could be referenced at runtime.

Scalar Fullselect

(fullselect)

The option scalar-fullselect belongs to the Natural SQL Extended Set.

A scalar-fullselect as supported in an expression is a fullselect - enclosed in parentheses - that returns a single row consisting of a single column value. If the fullselect does not return a row, the result of the expression is the null value. If more than one row is to be returned for a scalar-fullselect, an error occurs.

Row Value Expression

(scalar-expression,...)

A row-value-expression returns a single row that consists of one or more column values. The values can be specified as a list of expressions. The number of columns that are returned by the row-value-expression is equal to the number of expressions. row-value-expression can be used as an operand of several predicates (quantified, DISTINCT, comparison, and IN).