Rational Developer for System z, Version 7.6

Standard functions in ESQL

This topic briefly describes the standard functions that are included in the subset of ESQL that is supported by the service flow project tools.
Important: A function described in this topic (for example, CASE) might not support all the features or optional input parameters (such as the optional input parameter FORMAT) that the same-named function (CASE) supports in the full version of ESQL provided by WebSphere® Message Broker. Only the input parameters and features specified in this topic are supported.
Important: To fully understand a feature described in this topic, you should read the complete description of the feature in the documentation for WebSphere Message Broker (see Reference works describing ESQL).
The descriptions of functions in this topic use the following text conventions:

Many of the input parameter described in this topic can be specified as a literal value, as a message, or as an expression. If an input parameter is an expression then the function evaluates the expression and obtains the resulting value before continuing with processing.

This topic contains the following subtopics:

Variable manipulation functions

The table below shows the supported variable manipulation functions:

Table 1. Variable manipulation functions
Function: Description:

CASE source_value
     WHEN test_value
           THEN result_value
     WHEN test_value
           THEN result_value
   ...
[ELSE result expression]
END

At runtime, this function starts checking each of the specified WHEN...THEN clauses, in order, starting with the first one.
  • In each WHEN...THEN clause, the CASE function checks to see whether the test value specified in the WHEN...THEN clause matches (that is, equals) the source value that is stated immediately after the CASE keyword.
  • If the test value specified in the WHEN...THEN clause matches the source value, then the CASE function does not check any further WHEN...THEN clauses and returns the result value specified in the WHEN...THEN clause containing the matching source value.
  • If the CASE function checks all the WHEN...THEN clauses without finding a test value matching the source value, then the function returns NULL, or if the ELSE clause is specified then the function returns the result expression specified by the ELSE clause.
The following rules apply:
  • At least one WHEN...THEN clause must be specified.
  • The END keyword is required.
  • The test value in each WHEN...THEN clause must belong to a data type that can validly be compared with the data type of the source value.
CAST (source_expression AS data_type) Returns the value of the specified source expression cast into the specified data type.
  • The data type must be CHARACTER (or CHAR), INTEGER (or INT), or FLOAT.
  • The value returned belongs to the data type specified by the data type input parameter, or NULL if the source expression is NULL.
  • If the evaluated source expression cannot be cast to the specified data type, a runtime error is generated.

String processing functions

The table below shows the supported string processing functions:

Table 2. String processing functions
Function: Description:
LENGTH (source_string) Returns an integer specifying the number of elements in the source string.
  • The source string must be a CHARACTER string.
  • The value returned is an integer, or NULL if the source string is NULL.
LOWER (source_string) and LCASE (source_string) Returns a new string containing the source string mapped to lower case.
  • The source string must be a CHARACTER string.
  • The value returned is a CHARACTER string, or NULL if the source string is NULL.
OVERLAY (source_string PLACING overlay_string FROM start_position [FOR string_length]) Returns a new string containing the source string overlaid with the overlay string, starting at the specified start position in the source string and running for the specified string length.
  • The source string and the overlay string must be CHARACTER strings.
  • The start position and the string length must be integers.
  • The value returned is a CHARACTER string, or NULL if any input parameter is NULL.
  • If the string length parameter is not specified, then the function sets this value to the length of the overlay string.
SPACE (numeric_expression) Returns a new string consisting of the number of space characters specified by the input parameter.
  • The input parameter must be an integer.
  • The value returned is a CHARACTER string, or NULL if the input parameter is NULL. If the input parameter is zero or negative, then a zero-length string is returned.
SUBSTRING (source_string FROM start_position [FOR string_length]) Returns a new string containing the range of characters in the source string that is specified by the start position and string length parameters.
  • The source string must be a CHARACTER string. The start position and string length must be integers.
  • The value returned is a CHARACTER string, or NULL if any input parameter is NULL.
  • If the string length parameter is not specified, then the function sets this value to the number of characters remaining in the string, starting at the start position.
UPPER (source_string) and UCASE (source_string) Returns a new string containing the source string mapped to upper case.
  • The source string must be a CHARACTER string.
  • The value returned is a CHARACTER string, or NULL if the source string is NULL.

Numeric processing functions

For all the numeric processing functions described in this subtopic:
  • The input value can be any built-in numeric type (see NUMERIC data types).
  • The value returned is a FLOAT, or is NULL if the input parameter is NULL.

The table below shows the supported numeric processing functions:

Table 3. Numeric processing functions
Function: Description:
ACOS (numeric_expression) Returns the angle in radians of the specified cosine.
ASIN (numeric_expression) Returns the angle in radians of the specified sine.
ATAN (numeric_expression) Returns the angle in radians of the specified tangent.
COS (numeric_expression) Returns the cosine of the specified angle given in radians.
SIN (numeric_expression) Returns the sine of the specified angle given in radians.
TAN (numeric_expression) Returns the tangent of the specified angle given in radians.
LOG (numeric_expression) Returns the natural logarithm of the specified input value.
LOG10 (numeric_expression) Returns the logarithm to base 10 of the specified input value.
SQRT (numeric_expression) Returns the square root of the specified input value.

Date-time processing functions

Note: With these date-time processing functions, as with all ESQL functions that take no parameters, parentheses () are not allowed following the function name.

The table below shows the supported date-time processing functions:

Table 4. Date-time processing functions
Function: Description:
CURRENT_DATE Returns an 8-character string containing yyyymmdd.
CURRENT_TIME Returns an 8-character string containing HHmmsshh.
TIMESTAMP Returns a 16-character string containing yyyymmddHHmmsshh.


Terms of use | Feedback

This information center is powered by Eclipse technology. (http://www.eclipse.org)