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 type CASE expression has no expression following the CASE keyword and each of its when clauses is a condition that is evaluated such as i<8. A Simple type CASE expression has an expression 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 function

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.

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.

Using host variables in an SQL statement

Host variables can be used in an SQL statement to represent a value that will be substituted in at execution time. The host variable syntax is a symbol followed by a string. For example, :companyname. The symbol used depends on the what syntax your database vendor uses to denote variables.

• For Sybase, SQLServer and MySQL, you would use @NAME where NAME is the name of the host variable.
  .
• For all others database vendors, you would use :NAME where NAME is the name of the host variable.

You can use a host variable in your SQL statement in place of an expression. The Expression Builder can be used to create a host variable or you can simply type a host variable in place of an expression in the SQL query builder views.

When you execute your statement in the SQL builder you will be prompted to substitute a value of the appropriate type for the host variable.

_________________

Author: Mikalai Zaikin. Please Click Here to visit Authors site for any updates and changes to the study notes.

First Page: WebSphere V5.0 : Building Expressions