Value Set Aggregate Functions in the Expression Builder
Use value set aggregate functions when you want to aggregate a list of values provided by the select SQL query of the user-defined query into a single value.
The expression causes an error in the calculation process if the user-defined query provides a list of values without using a value set aggregate function. If a user-defined query returns a list of values and you don't use a value set aggregation function around it, then the expression will remain valid but the earnings calculation will fail. The description of a user-defined query includes whether it needs an aggregate function.This table provides details about value set aggregate functions.
Detail |
Description |
---|---|
Syntax |
SUM_VALUE_SET ( User Defined Query()* ) COUNT_VALUE_SET ( User Defined Query()* ) MAX_VALUE_SET ( User Defined Query()* ) MIN_VALUE_SET ( User Defined Query()* ) AVG_VALUE_SET ( User Defined Query()* ) COUNT_DISTINCT_VALUE_SET ( User Defined Query()* ) |
Inputs |
User-defined query (required) which returns a list of values |
Description |
It will work similar to the SQL Aggregate Functions SUM, COUNT, MAX, MIN, AVG, and 'COUNT DISTINCT' and will perform the aggregation on the list of values provided by the user-defined query. During calculation, this will add the aggregate function to the Value Column Name text box (within the SELECT clause of the SQL query) of the associated table validated value set. |
Example |
Target query to add to a user-defined query: Perform the following setup steps:
UDQ 1 () could return: 1000, 2000, 3000 while SUM_VALUE_SET ( UDQ 1 () ) would return 6000. |
Expression Usage |
Unlike existing aggregate functions, value set aggregate functions don't change the usage of the expression to a per interval or a group-by scenario. Add an existing aggregation function to the user-defined query to do so. |
Exceptions |
In cases where the user-defined query doesn't return any records, its output will be null. In such cases COUNT_VALUE_SET and COUNT_DISTINCT_VALUE_SET will return 0 while SUM_VALUE_SET, MAX_VALUE_SET, MIN_VALUE_SET, and AVG_VALUE_SET will return null. To avoid these scenarios, it's advisable to use the NVL function around the user-defined query. |
This table shows examples of expressions and their usage.
Expression |
Usage |
---|---|
|
This is a valid expression which can be used in a per event or individual scenario. |
|
This is a valid expression. The SUM around the User Defined Query was added to make the expression valid for a per interval or group-by scenario. In this case, the user-defined query returns only one value, otherwise the earnings calculation will produce an error. |
|
This is a valid expression. The SUM_VALUE_SET is added to total the list of values provided by the user-defined query. The MAX around SUM_VALUE_SET is added to make the expression valid for a per interval or group-by scenario |