

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:
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).
Common Set Syntax:
COUNT


( 
Extended Set Syntax:

( 

scalarexpression  )  
* 

( 

scalarexpression )  

(scalarexpression1,scalarexpression2) 
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.
ROW CHANGE

TIMESTAMP 
FOR
tabledesignator 

TOKEN 
A ROW CHANGE
expression returns a token or a
timestamp that represents the last change to a row.
orderedOLAPspecification 
RANK 
( ) OVER ([windowpartitionclause]
windoworderclause)


DENSE_RANK 
ROW_NUMBER ( )
OVER ([windowpartitionclause]
[windoworderclause])

aggregatefunction
OVER ( [windowpartitionclause])


RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING 

windoworderclause 

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


BETWEEN
groupbound1
AND
groupbound2 



PARTITION BY
partitioningexpression,...

ORDER
BY {sortkeyexpression 
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 selectlist, 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.
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 DSN8910EMP WHERE SALARY+BONUS > 30000 ORDER BY LASTNAME;
timezonespecificexpression
Specifies a timestamp with time zone value:
AT
LOCAL
or
AT TIME
ZONE
AT
LOCAL
functioninvocation 
{AT LOCAL }

AT TIME
ZONE
functioninvocation 
{AT TIME ZONE }

functioninvocation 
specialregister
A reference to a special register returns a scalar value.
For more information on the special registers that are supported by Natural, see specialregister in the section Syntactical Items Common to Natural SQL Statements in the Database Management System Interfaces documentation.
scalarfunction
A scalar function is a builtin function that can be used in the construction of scalar computational expressions.
For information on the scalar functions that are supported by the Natural, see scalarfunction in the section Syntactical Items Common to Natural SQL Statements in the Database Management System Interfaces documentation.
labeledduration
scalarexpression 

A labeledduration
denotes a specific unit of time as expressed by a number which can be an
expression followed by one of the duration keywords.
labeledduration
does not
conform to standard SQL, and is therefore supported by the Natural
SQL Extended Set
only.
caseexpression
CASE

ELSE

END

A caseexpression
does
not conform to standard SQL and is therefore supported by the Natural
SQL Extended Set
only.
WHEN
searchcondition
THEN

A Searched When Clause does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.
See details on searchcondition
.
scalarexpression  WHEN
scalarexpression THEN

A Simple WHEN
Clause does not conform to standard
SQL and is therefore supported by the Natural
SQL Extended Set
only.
castexpression
CAST
(scalarexpression
AS datatype)

A CAST
expression does not conform to standard SQL
and is therefore supported by the Natural
SQL Extended Set
only.
The option
userdefinedfunctionreference
belongs
to the Natural SQL Extended
Set. This option enables you to invoke any userdefined function.
Arguments have to be placed in brackets and separated by commas. The
userdefined function must be declared in the target RDBMS.
The option
sequencereference
belongs to the
Natural SQL Extended
Set.

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.
(fullselect) 
The option
scalarfullselect
belongs to the
Natural SQL Extended
Set.
A scalarfullselect
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 scalarfullselect
, an
error occurs.
(scalarexpression,...) 
A rowvalueexpression
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 rowvalueexpression
is equal to
the number of expressions.
rowvalueexpression
can be used as an
operand of several predicates (quantified,
DISTINCT
,
comparison,
and IN
).