Version 6.3.13 for Windows
 —  Statements  —

Scalar Expressions

+
-

factor
(scalar-expression)

                             

scalar-expression scalar-operator scalar-expression

This document covers the following topics:


Scalar Expression

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

Concerning reference priority, scalar expressions behave as follows:

Top of page

Scalar Operator

+

-
*
/

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.

Top of page

Factor

atom
column-reference
aggregate-function
special-register

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.

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

USER

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

Top of page

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

Top of page