Scalar Expressions

Scalar Expressions

Scalar Expression

Scalar Operator

Factor

Row Value Expression

Scalar Expression

Scalar Operator

Factor

Row Value Expression

Atom

Column Reference

Aggregate Function

ROW CHANGE Expression

OLAP Specification

Time-Zone-Specific Expression

Special Register

Scalar Function

Labeled Duration

Case Expression

Cast Expression

User-Defined Function Reference

Sequence Reference

Scalar Fullselect

Atom

Column Reference

Aggregate Function

ROW CHANGE Expression

OLAP Specification

Time-Zone-Specific Expression

Special Register

Scalar Function

Labeled Duration

Case Expression

Cast Expression

User-Defined Function Reference

Sequence Reference

Scalar Fullselect

On this page

DISTINCT

`ordered-OLAP-specification`

`lag-function`

`lead-function`

`numbering-specification`

`aggregation-specification`

`aggregate-function`

`OLAP-column-function`

`first-value-function`

`last-value-function`

`nth-value-function`

`ratio-to-report-function`

`window-aggregation-group-clause`

`group-start`

`group-between`

`group-bound-1`

`group-bound-2`

`group-end`

`window-partition-clause`

`window-order-clause`

`AT LOCAL`

`AT TIME ZONE`

Searched WHEN Clause

Simple WHEN Clause

DISTINCT

ordered-OLAP-specification

lag-function

lead-function

numbering-specification

aggregation-specification

aggregate-function

OLAP-column-function

first-value-function

last-value-function

nth-value-function

ratio-to-report-function

window-aggregation-group-clause

group-start

group-between

group-bound-1

group-bound-2

group-end

window-partition-clause

window-order-clause

AT LOCAL

AT TIME ZONE

Searched WHEN Clause

Simple WHEN Clause

`ordered-OLAP-specification`

`lag-function`

`lead-function`

`numbering-specification`

`aggregation-specification`

`aggregate-function`

`OLAP-column-function`

`first-value-function`

`last-value-function`

`nth-value-function`

`ratio-to-report-function`

`window-aggregation-group-clause`

`group-start`

`group-between`

`group-bound-1`

`group-bound-2`

`group-end`

`window-partition-clause`

`window-order-clause`

`AT LOCAL`

`AT TIME ZONE`

+
-

factor
(scalar-expression)

scalar-expression scalar-operator scalar-expression

This document covers the following topics:

Scalar Expression
Scalar Operator
Factor
Row Value 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).

	`+`
	`-`
	`*`
	`/`
	`\| \|`
	`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.

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

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

parameter
constant

An atom can be either a parameter or a constant.

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.

Note
A table name in this context must not be qualified explicitly with an authorization identifier. Use a correlation name instead if you need a qualified table name.

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

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
VAR_POP
VAR
VARIANCE_SAMP
VAR_SAMP

(

ALL
DISTINCT

scalar-expression )

CORRELATION
CORR
COVARIANCE
COVAR_POP
COVAR
COVARIANCE_SAMP
COVAR_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 
  ...

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

The following clauses of the OLAP Specification require the IBM Db2 Analytics Accelerator for z/OS:

CUME_DIST
PERCENT_RANK
NTILE
LAG
LEAD
FIRST_VALUE
LAST_VALUE
NTH_VALUE
RATIO_TO_REPORT

ordered-OLAP-specification
numbering-specification
aggregation-specification

	`CUME_DIST ( )`		`OVER` ([`window-partition-clause`] `window-order-clause`)
	`PERCENT_RANK ( )`
	`RANK ( )`
	`DENSE_RANK ( )`
	`NTILE (num-tile)`
	`lag-function`
	`lead-function`

`LAG` `( expression [ , offset [ , default [ ,`		`'RESPECT NULLS'`		`] ] ] )`
		`'IGNORE NULLS'`

`LEAD` `( expression [ , offset [ , default [ ,`		`'RESPECT NULLS'`		`] ] ] )`
		`'IGNORE NULLS'`

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

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

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

	`first-value-function`
	`last-value-function`
	`nth-value-function`
	`ratio-to-report-function`

`FIRST_VALUE` `( expression [ ,`		`'RESPECT NULLS'`		`] )`
		`'IGNORE NULLS'`

`LAST_VALUE` `( expression [ ,`		`'RESPECT NULLS'`		`] )`
		`'IGNORE NULLS'`

NTH_VALUE ( expression , nth-row )

RATIO_TO_REPORT

( expression )

ROWS
RANGE

group-start
group-between
group-end

UNBOUNDED PRECEDING
unsigned-constant PRECEDING
CURRENT ROW

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

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

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

UNBOUNDED FOLLOWING
unsigned-constant FOLLOWING

PARTITION BY partitioning-expression,...

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

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

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

{

AT
                                                  LOCAL

}

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

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

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 Natural, see scalar-function in the section Syntactical Items Common to Natural SQL Statements in the Database Management System Interfaces documentation.

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

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.

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.

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

The option user-defined-function-reference belongs to the Natural SQL Extended Set. This option 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.

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.

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

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

ORDER BY {sort-key-expression

NULLS LAST

ASC NULLS FIRST

DESC NULLS FIRST

DESC NULLS LAST