Datamart Query Expressions

 

This list is valid ONLY for direct Datamart / Data Source queries, not for cached (table context) tables queries. For cached tables we use the H2 database which has a slightly different syntax.

Expressions

  • The expression string supports:

    • Variables and functions

    • Number expressions (resulting in either an Integer or BigDecimal, but not double/float) 

    • String expressions

  • Literal strings have to be quoted using the double quotes (")

  • Supported operators are: ( ) + - * / ^ and or < <= > >= = <> % not inv

The + operator can be used for the concatenation (i.e., "a" + "b" -> "ab") in Data Source and Datamart field expressions. It is not applicable as an expression within Charts.

General Functions

Function

Description

Function

Description

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)

NULLIF(arg1,arg2)

Returns the first expression if the two expressions are not equal.

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)

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

Aggregation Functions for Group By

Function

Description

Function

Description

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)



ANY(arg1)



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.

Statistical Aggregation Functions

Function

Description

Function

Description

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)

Functions Used Within Groups

Function

Description

Function

Description

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

 

Found an issue in documentation? Write to us.

Â