+ -

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.

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.