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 Query
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.