SQL expressions that you can build in the Expression Builder wizard

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


Feedback