1. Using Incentive Compensation
  2. Troubleshooting User-Defined Queries

Troubleshooting User-Defined Queries

When you create user-defined queries, you may have an issue like no records in the results or an invalid expression. Here are some things to check if you run into trouble.

The User-Defined Query Returns No Records

When the user-defined query doesn't return any records, its output will be null. An expression that returns null defaults to the value 0. To avoid this, use the NVL function with the User Defined Query. Here are examples of earnings calculation output results from expressions that include the user-defined query UserDefinedQuery()that returns no values.

Expression

Earnings Calculation Output

5 + UserDefinedQuery ()

0 (The user-defined query returns null, but the expression defaults to 0)

SUM_VALUE_SET ( UserDefinedQuery() )

MAX_VALUE_SET ( UserDefinedQuery() )

MIN_VALUE_SET ( UserDefinedQuery() )

AVG_VALUE_SET ( UserDefinedQuery() )

0 (This expression returns null but the earnings amount defaults to 0)

COUNT_VALUE_SET ( UserDefinedQuery() )

COUNT_DISTINCT_VALUE_SET ( UserDefinedQuery () )

0

5 + NVL (UserDefinedQuery () , -1)

4

IS_NULL (UserDefinedQuery () )

TRUE

The Expression is Invalid

You have an expression that contains a user-defined query, and the expression is invalid. Check these possible causes.

  • Have you used mathematical operations on different data types? For example, a user-defined query returning a number can't be added to Participant.Currency Code which is a string.

  • Check whether you need to use or remove the aggregation function around the user-defined query to match the rest of the expression components. For example, the MAX aggregation function needs to be added to this expression SUM (Credit.Credit Amount) + MAX ( SUM_VALUE_SET ( UserDefinedQuery () ) ).

  • Do the number, order, and data type of the inputs passed to the user-defined query match the SQL query used in its value set?

  • Check with the administrator whether the SQL query used in its value set has consecutive natural numbers as the sequence for its inputs (:1, :2).

  • Check if the Value Set Aggregated functions only have user-defined queries as inputs. Not even User-defined functions can be passed to these.

  • Have any Boolean returning logical operators been used outside the CHOICE function?

  • Check the number or data type of the inputs passed to the logical operations.