An expression specifies a value. It can be a simple value, consisting
of only a constant or a column name, or it can be more complex. You can use
the Expression Builder wizard to build complex expressions or subqueries.
You can use the Expression Builder wizard to build the following types
of expressions:
- Function
- CASE - searched or simple
- CAST
- Constant
- Subquery
- Build up expressions by operators
- Function
- A function is an operation that is denoted by a function name followed
by a pair of parentheses that enclose the specification of zero or more arguments.
A function returns a value.
Functions are classified as column functions,
scalar functions, row functions, or table functions.
- The argument of a column function is a collection of like values (a column).
The function returns a single value (possibly null), and can be specified
in an SQL statement where an expression can be used.
- The arguments of a scalar function are individual scalar values, which
can be of different types. The function returns a single value (possibly null),
and can be specified in an SQL statement wherever an expression can be used.
- The argument of a row function is a structured type. The function returns
a row of built-in data types and can only be specified as a transform function
for a structured type.
- The arguments of a table function are individual scalar values, which
can be of different types. The function returns a table, and can be specified
only within the FROM clause of a SELECT statement.
- CASE - searched or simple
- CASE expressions allow an expression to be selected based on the evaluation
of one or more conditions. A CASE expression contains one or more when clauses
of either searched or simple type. A searched CASE expression has no condition
following the CASE keyword and each of its when clauses is a search
condition that is evaluated, such as i<8. A simple CASE expression has
a condition following the CASE keyword. The expression is compared with the
expression following each when clause. The value of the CASE expression
is the value of the result-expression following the first when clause
in the CASE expression that evaluates to true. If a when clause does
not evaluate to true, the else clause determines the value of the CASE
expression.
- CAST
- A CAST expression is a function that is used to convert instances of a
data type (origin) into instances of a different data type (target). A CAST
function takes the following form CAST( expr AS datatype).
The result of the expression expr is converted to the type datatype. For
example, CAST (XMLTEST.EMPLOYEE.SALARY AS INTEGER) converts the values in
the column SALARY to type integer.
Note: SQL support in the SQL builder is
dependent on the level of support that is provided by your database vendor.
CAST expression support:
- DB2® -
CAST expression AS expression
- MS SQL Server - CAST expression AS data_type
- Oracle - TRANSLATE is unsupported
- Constant
- A constant specifies a value. A constant can be a string or a number.
Numeric constants can be an integer, floating-point, or decimal. A string
constant can be a character string constant, a hexadecimal constant, or a
host variable name. A host variable name is denoted by a name preceded by
a colon such as :var and it is replaced by a value when the statement
is run.
- Subquery
- A subquery is a SELECT, WITH, or FULLSELECT statement that is nested within
another SQL statement. The expression value is the result of the subquery.
- Build up expressions by operators
- You can build an expression by applying operators to columns or expressions
such as x+y where x is a column and y is an expression.
For more information on SQL expressions, see the documentation for your
database product.