Building expressions

Using the Expression Builder wizard you can build complex expressions or subqueries. 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.

The types of expressions that can be built using the Expression Builder wizard are:

Function

A function is an operation that is denoted by a function name followed by a pair of parentheses enclosing 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 argument(s) 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 argument(s) 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 - search or simple type

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 Search or Simple type. A Search CASE expression has no condition following the CASE keyword and each of its when clauses is a 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 function 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 Create A New SQL Statement wizard and SQL Builder is dependent on the level of support 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 executed.

Subquery

A subquery is a SELECT, WITH, or FULLSELECT statement nested within another SQL statement. The expression value is the result of the subquery.

Build up expressions by operators

An expression can be built by applying operators to columns or expressions such as x+y where x is a column and y is an expression.
Creating an expression

You can start the Expression Builder wizard from anywhere in the SQL Builder where an expression can be used in a statement (for example, the Column column in the Design view of a SELECT statement).

To create an expression:

  1. Switch to the Data perspective.

  2. Open your statement in the SQL Builder.

  3. Double-click the cell in which you want to create the expression.

  4. Click Build expression in the list. The Expression Builder wizard opens.

  5. Select the type of expression that you want to build, and then click Next.

  6. Specify information for the fields in the wizard, and then click Finish. For information about a field, click it and then press F1.

For more information on SQL expressions, see the documentation for your database product or see the SQL Reference for DB2 at www.ibm.com/software/data/technical/BOOK/. Under the Featured books heading, click the "View page" link for DB2 Product Manuals (including Business Intelligence). Both volumes of SQL Reference are available in PDF format under the Reference heading.

After your expression is created, you can modify it by clicking twice in the cell that contains the expression and selecting to edit or replace the expression.

Editing an expression

To edit an existing expression:

  1. Switch to the Data perspective.

  2. Open your statement in the SQL Builder.

  3. Double-click the cell that contains the expression that you want to edit.

  4. Click Edit expression in the list. The Expression Builder wizard opens and shows your expression.

  5. Edit the expression, and then click Finish. For information about a field, click it and then press F1.
Replacing an expression

To replace an existing expression:

  1. Switch to the Data perspective.

  2. Open your statement in the SQL Builder.

  3. Double-click the cell that contains the expression that you want to edit.

  4. Click Replace expression in the list. The Expression Builder wizard opens.

  5. Select the type of expression with which to replace the current expression, and then click Next.

  6. Specify information for the fields in the wizard, and then click Finish. For information about a field, click it and then press F1.

 

Parent topic

Editing an SQL statement