Scalar Expressions

#### Search Results

Search

# Scalar Expressions

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

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.

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

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

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

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 |

#### ordered-OLAP-specification

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

#### lag-function

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

#### lead-function

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

#### numbering-specification

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

#### aggregation-specification

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

#### aggregate-function

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

#### OLAP-column-function

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

#### first-value-function

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

#### last-value-function

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

#### nth-value-function

`NTH_VALUE` `( expression` | `, nth-row )`

#### ratio-to-report-function

`RATIO_TO_REPORT` `( expression )`

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

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

### On this page

- Scalar Expression
- Scalar Operator
- Factor
- Row Value Expression