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:
- ESQL keywords and function names are capitalized.
- Input parameters are italicized and in lower case.
- Optional input parameters to functions are enclosed in square brackets
[ ].
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. |