Query Expressions (Agents)

ISNULL(arg1)

Returns a Boolean value indicating whether a specified expression contains no valid data (null).

IF(cond, value when true, value when false)

Evaluates a condition. Argument2 and 3 have to have the same data type.

Sample SQL: case country when 'Italy' then 'OK' else 'NAH' end

IFNULL(arg1,arg2)

== IF(ISNULL(arg1), arg2, arg1)

== COALESCE(arg1,arg2)

Example:

IFNULL(Volume, 0)

When used in filters with an expression such as Volume >= 0, if the Volume value is NULL, the filter will not match any records. This is because, in SQL, any comparison involving NULL yields an unknown result, which is considered false in the context of filtering conditions. Consequently, if Volume is NULL, the condition Volume >= 0 evaluates to false, and the record is excluded.

However, if you modify the filter to use IFNULL, as in IFNULL(Volume, 0) >= 0, if Volume is NULL, the IFNULL function substitutes it with 0, making the comparison 0 >= 0, which evaluates to true. Therefore, records with NULL values for Volume will be included in the results under this filter, ensuring that NULL values are treated as zero for the purpose of the comparison.

NULLIF(arg1,arg2)

Returns the first expression if the two expressions are not equal. This function is used, for example, to calculate an average price and to prevent division by zero errors.

Example 1 : Revenue / NULLIF(Volume, 0)

If the Volume is zero, the NULLIF() function will replace it with a null, and a division by null yields null, avoiding an error message. If the Volume is non-zero, its existing value will be used.

Example 2: Margin Rate = SUM(grossMargin)/NULLIF(Revenue,0)

TOSTRING(arg1)

==  CAST(arg AS VARCHAR)

TODATE(arg1)

==  CAST(arg AS DATE)

PARSE(arg1,arg2)

Argument1 is literal, argument2 is literal string, e.g. "STRING".

For argument2, allowed values are: STRING, NUMBER, DATE, BOOLEAN, DATETIME, LOB, LONG

SQL0 - SQL5(arg1-arg6)

An SQL query with formula: SQL<n>("SQL expression with <n> inputs represented by %s placeholders", field1, ..., field n)
Example: SQL1("date_part('month',%s)",InvoiceDate)

Note that Pricefx supports only SQL expressions that return numeric values.

CEILING(arg1)

The nearest integer greater than or equal to the argument.

FLOOR(arg1)

The nearest integer less than or equal to the argument.

ROUND(arg1,arg2)

Rounds to the specified number of decimals: round(42.4382, 2) = 42.44

SUM(arg1)

Sum of the expression across all input values.

AVG(arg1)

Average of the expression across all input values.

MIN(arg1)

Minimum value of the expression across all input values.

MAX(arg1)

Maximum value of the expression across all input values.

COUNT(arg1)

Number of input rows for which the value of the expression is not null.

COUNT_DISTINCT(arg1)

Number of all distinct non-null values specified by the expression.

EVERY(arg1)

Returns true if the expression is true for all input values; otherwise returns false.

ANY(arg1)

Returns true if the expression is true for at least one input value; otherwise returns false.

PERCENTILE_DISC(arg1,arg2)

precentile_disc(expr, fraction): Discrete percentile: The first input value whose position in the ordering equals or exceeds the specified fraction

PERCENTILE_CONT(arg1,arg2)

precentile_cont(expr, fraction): Continuous percentile: The first input value whose position in the ordering equals or exceeds the specified fraction. Interpolating between adjacent input items if needed.

corr(Y,X)

Correlation coefficient

covar_pop(Y,X)

Population covariance

covar_samp(Y,X)

Sample covariance

regr_avgx(Y,X)

Average of the independent variable (sum(X)/N)

regr_avgy(Y,X)

Average of the dependent variable (sum(Y)/N)

regr_count(Y, X)

Number of input rows in which both expressions are non-null

regr_intercept(Y,X)

Y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs

regr_r2(Y,X)

Square of the correlation coefficient

regr_slope(Y,X)

Slope of the least-squares-fit linear equation determined by the (X, Y) pairs

regr_sxx(Y,X)

Sum(X^2) - sum(X)^2/N ("sum of squares" of the independent variable)

regr_sxy(Y,X)

Sum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable)

regr_syy(Y,X)

Sum(Y^2) - sum(Y)^2/N ("sum of squares" of the dependent variable)

stddev_pop(expr)

Population standard deviation of the input values

stddev_samp(expr)

Sample standard deviation of the input values

var_pop(expr)

Population variance of the input values (square of the population standard deviation)

var_samp(expr)

Sample variance of the input values (square of the population standard deviation)

PERCENT_RANK(arg1)

Relative rank of the current row: (rank - 1) / (total rows - 1)

CUME_DIST(arg1)

Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)

NTILE(arg1,arg2)

ntile(expr, num_buckets): Integer ranging from 1 to max num_buckets, dividing the partition as equally as possible

BIN_IDX(arg1,arg2)

bin_idx(expr, num_buckets): Index of bin from 1 to max num_buckets, with the equiwidth bins ranging from min(expr) to max(expr)

BIN_ABS(arg1,arg2)

bin_abs(expr, num_buckets): Mid-point of bin, dividing the min(expr) - max(expr) range into equal width num_buckets

BIN_PCT(arg1,arg2)

bin_pct(expr, num_buckets): Mid-point of bin as percentage, dividing the min(expr) - max(expr) range into equal width num_buckets