|
|
|
|
|
|
|
|
||||||||||||||||
| 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:
|
|
|
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.
|
|
|
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).
Common Set Syntax:
|
|
COUNT
|
|
|
||||
|
|
|
|
( |
|
|||
Extended Set Syntax:
|
|
|
|
|
( |
|
|
|
|
scalar-expression |
|
) |
|
* |
||||||||||||
|
|
|
|
( |
|
|
|
scalar-expression ) | |||||
|
|
|
|
(scalar-expression-1,scalar-expression-2) | |||||||||
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.
ROW CHANGE
|
|
TIMESTAMP |
|
FOR
table-designator |
TOKEN |
A ROW CHANGE expression returns a token or a
timestamp that represents the last change to a row.
The following clauses of the OLAP Specification require the IBM Db2 Analytics Accelerator for z/OS:
CUME_DIST
PERCENT_RANK
NTILE
LAG
LEAD
FIRST_VALUE
LAST_VALUE
NTH_VALUE
RATIO_TO_REPORT
|
|
ordered-OLAP-specification |
|
|
|
CUME_DIST (
) |
|
OVER ([window-partition-clause]
window-order-clause)
|
|
PERCENT_RANK (
) |
||||
RANK (
) |
||||
DENSE_RANK ( ) |
||||
NTILE
(num-tile) |
||||
lag-function |
||||
lead-function |
||||
LAG
( expression [ ,
offset [ , default [ ,
|
|
'RESPECT NULLS' |
|
] ] ]
) |
'IGNORE
NULLS' |
LEAD
( expression [ ,
offset [ , default [ ,
|
|
'RESPECT NULLS' |
|
] ] ]
) |
'IGNORE
NULLS' |
ROW_NUMBER ( )
OVER ([window-partition-clause]
[window-order-clause])
|
|
|
aggregate-function |
|
OVER ( [window-partition-clause])
|
||
| OLAP-column-function | |||||
|
|
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING |
|
|||
| window-order-clause |
|
|
|
||
|
|
AVG function |
|
| CORRELATION function | ||
| COUNT function | ||
| COUNT_BIG function | ||
| COVARIANCE function | ||
| MAX function | ||
| MIN function | ||
| STDDEV function | ||
| SUM function | ||
| VARIANCE function |
|
|
first-value-function |
|
| last-value-function | ||
| nth-value-function | ||
| ratio-to-report-function |
FIRST_VALUE ( expression
[ , |
|
'RESPECT NULLS' |
|
] ) |
'IGNORE
NULLS' |
LAST_VALUE ( expression
[ , |
|
'RESPECT NULLS' |
|
] ) |
'IGNORE
NULLS' |
NTH_VALUE (
expression
|
,
nth-row ) |
|
RATIO_TO_REPORT (
expression )
|
|
|
|
|
|
|
|
|
|
|
BETWEEN
group-bound-1
AND
group-bound-2 |
|
|
|
|
|
|
|
|
|
|
|
|
PARTITION BY
partitioning-expression,...
|
ORDER
BY {sort-key-expression |
|
|
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 select-list, 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 DSN8910-EMP WHERE SALARY+BONUS > 30000 ORDER BY LASTNAME;
time-zone-specific-expression
Specifies a timestamp with time zone value:
AT
LOCAL or
AT TIME
ZONE
AT
LOCAL|
|
function-invocation |
|
{AT LOCAL}
|
|||||
AT TIME
ZONE|
|
function-invocation |
|
{AT TIME ZONE}
|
|
function-invocation |
|
||
special-register
A reference to a special register returns a scalar value.
For more information on the special registers that are supported by Natural, see special-register in the section Syntactical Items Common to Natural SQL Statements in the Database Management System Interfaces documentation.
scalar-function
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 Natural, see scalar-function in the section Syntactical Items Common to Natural SQL Statements in the Database Management System Interfaces documentation.
labeled-duration
| scalar-expression |
|
|
|
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.
case-expression
CASE
|
|
|
|
ELSE
|
|
|
|
END
|
A case-expression does
not conform to standard SQL and is therefore supported by the Natural
SQL Extended Set
only.
WHEN
search-condition
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 search-condition.
| scalar-expression |
|
WHEN
scalar-expression THEN
|
|
 |
|
|
A Simple WHEN Clause does not conform to standard
SQL and is therefore supported by the Natural
SQL Extended Set
only.
cast-expression
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 option enables 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.
The option
sequence-reference 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
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.
| (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).