Time-Series and Aggregation
C- SQL Function Description
ABS Returns the absolute value of a number.
AVG Returns the average value (arithmetic mean) of a set of numeric values.
CASE The CASE expression is described in detail in “CASE Expression” on page 323.
CAST Converts a value from one Business Activity Monitoring type to another Business Activity Monitoring type.
CEIL Returns the smallest integer, rounded up from zero, greater than or equal to a number.
CHARACTER_LENGTH Returns the length of a string.
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.
concatSet Returns an alphabetically ordered set of strings.
COUNT Returns the count of rows in a view or set.
CURRENT Returns a value from the latest or last row in a set.
CURRENT_TIMESTAMP Returns the current date and time in the server time zone.
CURRENT_USER Returns the login name of the current user.
DATE_ADD Adds a duration of time to a date-time value.
DATE_DIFF Subtracts a duration from a date-time value.
DISPLAY_MONEY Formats a number as a currency value.
EXP Returns e raised to a specific power.
FLOOR Returns largest integer less than or equal to an expression.
Adobe LiveCycle ES Functions
Business Activity Monitoring Server Reference 115
gammaDist Returns the gamma distribution of a value.
GREATEST Returns the greatest of a list of expression results.
IS_RAISED Returns true when the specified alert is in a raised state.
LAST_DAY Returns the date of the last day of the month that contains a specified date.
LEAST Returns the least value of a list of expressions.
LOG Returns the logarithm of a number from a specific base.
logNormDist Returns the cumulative lognormal distribution of a value.
LOWER Converts all uppercase characters in a string to lower case.
LPAD Inserts one or more instances of a string into the start of another string.
LTRIM Removes characters from the start of a string.
MAX Returns the maximum value from a set.
median Returns the median (middle) number in a set.
MIN Returns the minimum value from a set.
MOD Returns the modulus (remainder) of a division.
mode Returns the most frequently occurring number in a set.
MOV_function Limits the rows used in a set function calculation to a set of the latest rows in the view.
MOV_AVG Returns the moving average value (arithmetic mean) of a moving window set of numeric values.
MOV_COUNT Returns the count of rows in a moving window set.
MOV_MAX Returns the maximum value from a moving window set.
MOV_MIN Returns the minimum value from a moving window set.
MOV_SUM Returns the sum of a moving window set of numeric values.
MOV_STD_DEVIATION Returns sample standard deviation of a moving window set of numbers.
MOV_VARIANCE Returns the square of the sample standard deviation of a moving window set of numbers.
Adobe LiveCycle ES Functions
Business Activity Monitoring Server Reference 116
NTILE Determines the tier rank of each value in a set with respect to the entire set.
POSITION Returns the position of a character or string within a string.
POWER Returns a value raised to a specific power.
PREV Returns a value from the next to last row in a set.
PRIOR_VALUE Returns the prior value of a column, alias, or expression.
RANK Determines the rank of each value in a set with respect to the entire set.
RATIO_TO_REPORT Calculates the ratio of a value to the sum of the values for the entire set.
ROUND Returns a number rounded up to a specified count of decimal places.
RPAD Adds one or more instances of a string to the end of another string.
RTRIM Removes characters from the end of a string.
SAFE_DIVIDE Returns a quotient of two values, unless the quotient is 0, in which case it returns an alternate quotient value.
SIGN Identifies the arithmetic sign of a number.
SQRT Returns the square root of a number.
SUBSTRING Returns the portion of a string identified by position and length.
SUM Returns the sum of a set of numeric values.
SUM_OVER_GROUPS Returns a running sum of the numeric values ordered by the column specified in the arguments.
STD_DEVIATION Returns sample standard deviation of a set of numbers.
TIMESTAMP_DIFF Returns the interval of time between two timestamps.
TO_CHAR Converts a date-time to a character string.
TO_DATE Converts a character string to a date-time value.
TRUNC Truncates a number to a specific count of decimal places.
TUMBLE_AVG Returns the average value (arithmetic mean) of a tumbling window set.
Adobe LiveCycle ES Functions
Business Activity Monitoring Server Reference 117
TUMBLE_COUNT Returns the count of rows in a tumbling window set.
TUMBLE_MAX Returns the maximum value from a tumbling window set.
TUMBLE_MIN Returns the minimum value from a tumbling window set.
TUMBLE_SUM Returns the sum of a tumbling window set of numeric values.
TUMBLE_STD_DEVIATION Returns sample standard deviation of a tumbling window set of numbers.
TUMBLE_VARIANCE Returns the square of the sample standard deviation of a tumbling window set of numbers.
UPPER Converts all lowercase characters in a string to uppercase.
VARIANCE Returns the square of the sample standard deviation of a set of numbers.
yield
Adobe LiveCycle ES Functions
Business Activity Monitoring Server Reference ABS 118
ABS
This scalar function returns the absolute value of a number. Syntax
ABS( numeric )
Parameters
● numeric— An expression that evaluates to a numeric.
Return Type
Numeric, same data-type as numeric argument. Example
Return the difference in two persons ages, regardless of which is older.
SELECT ABS( father_age - mother_age ) AS "Difference of parents ages" FROM Family
SIGN() returns the arithmetic sign of a number.
AVG
This set function returns the average value (arithmetic mean) of a set of numeric values. Syntax
AVG( numeric )
Parameters
● numeric— An expression that evaluates to a numeric and which cannot reference a rank function
function. Typically the argument is a column in a view. Return Type
Numeric, same data-type as numeric argument. Remarks
Calculates the average of numeric in all rows in the referenced view. When using a GROUP BY Clause, the average applies to the numeric in each group.
SELECT AVG( pr_price ) "Average price" FROM Products Average price
--- 49.96
Example
The following example uses moving averages to produce results similar to a Moving Average Convergence/Divergence (MACD) indicator. (This is not a true MACD because it does not use an
Adobe LiveCycle ES Functions
Business Activity Monitoring Server Reference AVG 119
falls below its 9 day average and to buy when the MACD rises above the 9 day average. You can accomplish this by defining rules similar to this:
● Raise SELL when MACD > Nine_Day_MA
Lower SELL when MACD < Nine_Day_MA
● Raise BUY when MACD < Nine_Day_MA
Lower BUY when MACD > Nine_Day_MA To get these values you need two views:
● MACD_Base_View tracks the moving averages for each security symbol in the event stream. Note that
the Nine_Day_MA formula repeats the formulas for the other two averages. This is because you cannot reference an alias in another column of the same view.
SELECT
StockQuotes.SYMBOL AS Symbol,
MOV_AVG(StockQuotes_Event.CLOSE, Day, 26, StockQuotes.DATE) AS Twentysix_Day_MA,
MOV_AVG(StockQuotes_Event.CLOSE, Day, 12, StockQuotes.DATE) AS Twelve_Day_MA,
MOV_AVG((MOV_AVG(StockQuotes.CLOSE, Day, 12, StockQuotes.DATE) - MOV_AVG(StockQuotes.CLOSE, Day, 26, StockQuotes.DATE)), Day, 9, StockQuotes.DATE ) AS Nine_Day_MA
FROM StockQuotes
GROUP BY StockQuotes.SYMBOL
● MACD_View contains the last MACD values for each security stored in the base view: SELECT MACD_Base_View.Symbol AS Symbol,
MACD_Base_View.Nine_Day_MA AS Nine_Day_MA, (MACD_Base_View.Twentysix_Day_MA -
MACD_Base_View.Twelve_Day_MA ) AS MACD
FROM MACD_Base_View
median() returns the median (middle) number in a set.
mode() returns the most frequently occurring number in a set.
MOV_AVG() returns the moving average for a set.
Adobe LiveCycle ES Functions
Business Activity Monitoring Server Reference CASE 120
CASE
The CASE expression is described in detail in “CASE Expression” on page 323.
CAST
This scalar function converts a value from one Business Activity Monitoring type to another Business Activity Monitoring type.
Syntax
CAST( value AS vcDataType )
Parameters
● value— Value to convert.
● vcDataType— One of the C-SQL Data Types to convert to. ● INTEGER ● DECIMAL ● DOUBLE PRECISION ● VARCHAR ● TIMESTAMP ● BOOLEAN Return Type
Same as vcDataType argument. Remarks
Types are cast according the Order of Precedence table in “Data Type Conversion” on page 56. CAST() returns an error if a type cannot be cast as specified in an expression. For example, the following is an error because C-SQL attempts to cast ‘4.5’ to an INTEGER, but the decimal is an illegal character for INTEGER types:
3 < CAST( '4.5' AS INTEGER )
When casting from a decimal formatted column to a string, the result is zero-padded on the decimals to match the scale, just as when casting from a string to a decimal. For example,
CAST( '1.1' AS DECIMAL(5,4) ) --> 1.1000
Adobe LiveCycle ES Functions
Business Activity Monitoring Server Reference CEIL 121
Example
Cast a date string into a time-stamp:
SELECT CAST('1997-10-22' AS TIMESTAMP )