Datamart Query Expressions

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)

SAFEDIV(num, den, default)

Performs division while safely handling zero or null denominators. This function is available in Agents, Analytics charts, and Rollups. All arguments must be numeric.

Variants
- SAFEDIV(numerator, denominator, defaultValue)

Returns: defaultValue if the denominator is 0 or NULL.
- SAFEDIV(numerator, denominator)

Returns: NULL if the denominator is 0 or NULL.

If the numerator is NULL, the result is always NULL regardless of the denominator or default value.
The SAFEDIV function is available starting from version 16.3.

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.

WAVG(arg1, arg2)

Calculates the weighted average of arg1 by arg2. The concrete calculation formula is:

SUM(arg1 * arg2) / NULLIF(SUM(arg2), 0). 

Both arguments must be numeric.
The WAVG function is available starting from version 16.3.

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.

ANY_VALUE(arg1)

Returns an arbitrary value from the group. There is no guarantee which specific value is returned if multiple values exist. If the group contains NULL values, the function may return NULL unless all values in the group are non-null.
The ANY_VALUE function is available starting from version 16.3.

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