SELECT CAST('1997-10-22' AS TIMESTAMP ) FROM Foo;

“Data Type Conversion” on page 56 provides details about converting types.

TO_CHAR() converts the timestamp to a character string of specified format.

TO_DATE() converts a character string to a date.

CEIL

This scalar function returns the smallest integer, rounded up from zero, greater than or equal to a number. Syntax

CEIL( numeric )

Parameters

● numeric— Number to round.

Return Type

Same data type are numeric result. Example

CEIL(1234.56) returns 1235.00.

CEIL(-2.75) returns -2.00.

Adobe LiveCycle ES Functions

Business Activity Monitoring Server Reference CHARACTER_LENGTH 122

CHARACTER_LENGTH

This scalar function returns the length of a string. Syntax

CHARACTER_LENGTH( string )

Parameters

● string— String or VARCHAR expression result whose length to evaluate.

Return Type

INTEGER. Remarks Alternate spelling is:

CHAR_LENGTH( string )

Returns an integer that is the length of the string. Returns NULL if the string is NULL.

The length of a string is determined by its displayable characters, and not necessarily the storage length of the string. For example, a Unicode character requires 16-bits of storage — which might be considered as 2 characters of storage on some systems — but the actual character length is 1.

CONCAT

This scalar function returns a string that is the concatenation of two characters or strings. Syntax

CONCAT( string1, string2 )

Parameters

● string— A character string value or VARCHAR expression result.

Return Type

VARCHAR. Remarks

Returns string2 appended to the end of string1. Returns NULL if either string is NULL. The || operator (“String operators” on page 250) is identical to this function.

Adobe LiveCycle ES Functions

Business Activity Monitoring Server Reference concatList 123

Examples

CONCAT('a', 'b') returns 'ab'.

'a'||'b' returns 'ab'.

concatList() returns a string that is the concatenation of a list of characters or strings.

concatSet() returns an alphabetically ordered set of strings.

“String operators” on page 250 describes the || operator.

concatList

This scalar function returns a string that is the concatenation of a list of characters or strings.

Note: This function is provided as a sample UDF. To use it, you will need to load the function from the directory \samples\udf\jar\com\\manifest on the distribution CD. For information on how to load UDFs, see “User-Defined Functions” on page 346.

Syntax

concatList( string1, string2 [, … stringN ] )

Parameters

● string— An expression that evaluates to a VARCHAR

Return Type

VARCHAR. Remarks

Returns string2 appended to the end of string1, string3 appended to string2, and so on. Ignores NULL values unless all values are NULL, in which case returns an empty string.

Examples

concatList('a','b','c') returns 'abc'.

CONCAT() returns a string that is the concatenation of two characters or strings.

concatSet() returns an alphabetically ordered set of strings.

Adobe LiveCycle ES Functions

Business Activity Monitoring Server Reference concatSet 124

concatSet

This set function returns an alphabetically ordered set of strings.

Note: This function is provided as a sample UDF. To use it, you will need to load the function from the directory \samples\udf\jar\com\celequest\manifest on the distribution CD. For information on how to load UDFs, see “User-Defined Functions” on page 346.

Syntax

concatSet( stringExp )

Parameters

● stringExp— An expression that evaluates to a VARCHAR. Typically the argument is a column in a view. Return Type

VARCHAR. Remarks

Returns a string that is the ordered set of all the strings passed into the function. Ignores NULL values unless all values are NULL, in which case returns an empty string.

Examples

Consider this statement:

SELECT concatSet(item) AS item_list FROM GroceryList

If the items in GroceryList are presented as follows in this order:

'banana' 'egg' 'apple' 'donut' NULL 'carrot'

The order in item_list in the new view is:

'apple,banana,carrot,donut,egg'

Subsequently, if ‘bagel’ is added to GroceryList, the new order in the new view is:

'apple,bagel,banana,carrot,donut,egg'

CONCAT() returns a string that is the concatenation of two characters or strings.

concatList() returns a string that is the concatenation of a list of characters or strings.

Adobe LiveCycle ES Functions

Business Activity Monitoring Server Reference COUNT 125

COUNT

This set function returns the count of rows in a view or set. Syntax

COUNT( * )

Return Type

INTEGER. Remarks

Returns zero (0) if the view or set is empty. This is also known as the “count star” function.

Rows that include NULLs are counted.

MOV_COUNT() returns the count of a moving set.

TUMBLE_COUNT() returns the count of a tumbling set.

CURRENT

This set function returns a value from the latest or last row in a set. Syntax

CURRENT( columnName )

Parameters

● columnName— Column or alias to retrieve. Return Type

Same data-type as argument. Remarks

Returns a value from the latest row in the set based on the event timestamp. When all rows in the set have the same timestamp, returns the value from the last row in the set.

Adobe LiveCycle ES Functions

Business Activity Monitoring Server Reference CURRENT_TIMESTAMP 126

Example

Gather all stock feed bids and group them by stock symbol. The “current” row will always be the last one received, and as such, will contain the current bid price:

SELECT symbol, CURRENT(bid) AS Bid, MAX(bid) AS High, MIN(bid) AS LOW FROM Stock_feed

GROUP BY symbol

symbol Bid High Low

--- --- --- ---

K 31.25 31.28 30.72

IBM 80.79 80.04 82.55

VCLR 22.60 24.42 22.00

Moving set semantics

Cannot be used with a moving or tumbling set.

PREV() returns a value from the row previous to the current one.