|
|
|
+
|
|
|
factor |
|
|
||
-
|
(scalar-expression) | ||||||||
| scalar-expression | scalar-operator | scalar-expression | |||||||
This document covers the following topics:
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).
|
|
+
|
|
-
|
||
*
|
||
/
|
||
| |
|
||
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.
Common Set Syntax:
|
|
atom |
|
| column-reference | ||
| aggregate-function | ||
| special-register |
Extended Set Syntax:
A factor can consist of one of the items listed in the above diagram and described in the text below.
|
|
parameter |
|
| constant |
An atom can be either a parameter or a constant; see also the section Basic Syntactical Items.
|
|
table-name . |
|
column-name |
| correlation-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.
Common Set Syntax:
|
|
COUNT
|
|
(*) |
|
|||
(DISTINCT
column-reference)
|
|||||||
|
|
AVG
|
|
|||||
MAX
|
|
(DISTINCT
column-reference)
|
|
||||
MIN
|
([ALL]
scalar-expression)
|
||||||
SUM
|
|||||||
Extended Set Syntax:
|
|
COUNT
|
|
(*) |
|
|
||
(DISTINCT
column-reference)
|
|||||||
|
|
AVG
|
|
|||||
MAX
|
|||||||
MIN
|
|||||||
SUM
|
|||||||
COUNT_BIG
|
|||||||
STDDEV |
|||||||
STDDEV_POP
|
|
(DISTINCT
column-reference)
|
|
||||
STDDEV_SAMP
|
([ALL]
scalar-expression)
|
||||||
VAR
|
|||||||
VAR_POP
|
|||||||
VAR_SAMP
|
|||||||
VARIANCE
|
|||||||
VARIANCE_SAMP
|
|||||||
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:
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 ...
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.
Common Set Syntax:
USER
|
Extended Set Syntax:
|
|
USER
|
|
CURRENT TIMEZONE
|
||
CURRENT DATE
|
||
CURRENT TIME
|
||
CURRENT TIMESTAMP
|
||
CURRENT SQLID
|
||
CURRENT PACKAGESET
|
||
CURRENT SERVER
|
A reference to a special-register returns a scalar value.
With the exception of USER,
special-registers do not conform to standard SQL and
are therefore supported by the Natural SQL Extended Set only.
A scalar-function is a built-in function that can be used in the construction of scalar computational expressions.
For information on the scalar-functions that are supported by the Natural SQL Extended Set, see Natural SQL Statements - Syntactical Items, scalar-function in the Natural for DB2 documentation.
|
|
YEAR
|
|
YEARS
|
||
MONTH
|
||
MONTHS
|
||
DAY
|
||
DAYS
|
||
HOUR
|
||
HOURS
|
||
MINUTE
|
||
MINUTES
|
||
SECOND
|
||
SECONDS
|
||
MICROSECOND
|
||
MICROSECONDS
|
unit does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.
CASE
|
|
searched-when-clause
|
|
|
ELSE
|
|
NULL
|
|
|
END
|
| simple-when-clause | scalar-expression |
A case-expression does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.
WHEN
search-condition THEN
|
|
NULL
|
|
| scalar-expression |
A Searched When Clause does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.
See details on search-condition.
| scalar-expression |
|
WHEN
scalar-expression
THEN
|
|
NULL
|
|
| scalar-expression |
A Simple When Clause does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.
CAST
(scalar-expression
AS data-type)
|
A CAST expression does not conform to standard SQL and is therefore supported by the Natural SQL Extended Set only.
The option user-defined-function-reference belongs to the Natural SQL Extended Set. This options allows you to invoke any user-defined function. Arguments have to be placed in brackets and separated by commas. The user-defined function must be declared in the target RDBMS.
| NEXT VALUE FOR sequence-name |
| PREVIOUS VALUE FOR sequence-name |
The option sequence-reference belongs to the Natural SQL Extended Set.
This option allows 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 scalar-fullselect belongs to the Natural SQL Extended Set.
A scalar-fullselect 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 scalar-fullselect, an error occurs.
