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

## 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 special-register scalar-function length-stringunit labeled-duration |

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

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

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.

### Special Register

`special-register`

`USER`

With the exception of `USER`, the following special registers do not conform to standard SQL. They are specific to Db2 and belong to the Natural SQL Extended Set:

    CURRENT DATE
    CURRENT_DATE
    CURRENT TIME
    CURRENT_TIME
    CURRENT TIMESTAMP
    CURRENT CLIENT_ACCTNG
    CLIENT ACCTNG
    CURRENT CLIENT_APPLNAME
    CLIENT APPLNAME
    CURRENT CLIENT_USERID
    CLIENT USERID
    CURRENT CLIENT_WRKSTNNAME
    CLIENT WRKSTNNAME
    CURRENT DEGREE
    CURRENT TIMEZONE
    CURRENT SERVER
    CURRENT_TIMEZONE
    CURRENT_SERVER
    SESSION_USER
    CURRENT_PATH
    CURRENT SCHEMA
    CURRENT DECFLOAT ROUNDING MODE
    CURRENT LOCK TIMEOUT
    CURRENT PACKAGE PATH
    CURRENT REFRESH AGE
    CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION

A reference to a `special-register` returns a scalar value.

### Scalar Function

`scalar-function`

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

Scalar functions are specific to Db2 and belong to the Natural SQL Extended Set.

The scalar functions Natural for Db2 supports are listed below:

`COALESCE` `DATE` `TIME` `TIMESTAMP` `VALUE`

Each scalar function is followed by one or more scalar expressions in parentheses. The number of scalar expressions depends upon the scalar function. Multiple scalar expressions must be separated from one another by commas.

Example:

    SELECT NAME
      INTO NAME
      FROM SQL-PERSONNEL
      WHERE  VALUE(NAME, CITY)  = 'VIZAG'
           ...

### Length of String Unit

`length-stringunit`

Specifies the unit used for the length of a string. Commonly used for SQL scalar string functions. The supported length of string units are listed below:

`OCTETS` `CODEUNITS16` `CODEUNITS32`

where `OCTETS` specifies that the length is expressed in bytes, `CODEUNITS16` specifies that the length is expressed in 16-bit UTF-16 code units, and `CODEUNITS32` specifies that the length is expressed in 32-bit UTF-32 code units.

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

### On this page

- Scalar Expression
- Scalar Operator
- Factor