

scalarexpression scalaroperator scalarexpression 
This document covers the following topics:
A scalarexpression
consists
of a factor or other scalar
expressions including scalar operators.
Concerning reference priority, scalar expressions behave as follows:
When a nonqualified 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 userdefined variable (host variable).
+





*


/


 


CONCAT

A scalaroperator
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:
Extended Set Syntax:
atom

A factor
can consist of one
of the items listed in the above diagram and described in the text below.
parameter 
An atom can be either a
parameter
or a constant
.
columnname 
A columnreference
is a
column name optionally qualified by either a
tablename
or a correlationname
(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
tablename
or
correlationname
, it must be contained
in the corresponding table. If neither a
tablename
nor a
correlationname
is specified, the
respective column must be in one of the tables specified in the
FROM
clause (see Table
Expression).
COUNT


( 
SQL provides a number of special functions to enhance its basic retrieval power. The socalled SQL aggregate functions currently available and supported by Natural are:
Apart from COUNT(*)
, each of these functions operates
on the collection of scalar values in an argument (that is, a single column or
a scalarexpression
)
and produces a scalar value as its result.
Example:
DEFINE DATA LOCAL 1 AVGAGE (I2) ENDDEFINE ... SELECT AVG (AGE) INTO AVGAGE FROM SQLPERSONNEL ...
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 scalarexpression
.
DISTINCT
is not allowed with the
special function COUNT(*)
, which is provided to count all rows
without eliminating any duplicates.
specialregister
USER

A reference to a
specialregister
returns a scalar
value.
scalarfunction
A scalar function is a builtin 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:

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 SQLPERSONNEL WHERE VALUE(NAME, CITY) = 'VIZAG' ...