Supported functions and expressions

Queries with supported functions and expressions are accelerated on Informix® Warehouse Accelerator.

Aggregate functions and expressions

The following aggregate functions are supported by Informix Warehouse Accelerator:

AVG
COUNT
STDEV
SUM
VARIANCE

Aggregate functions with the DISTINCT keyword are supported. For example, COUNT DISTINCT(). The DISTINCT keyword is also supported in an SQL projection clause like SELECT DISTINCT stock_num, manu_code FROM items;. However, you cannot use the DISTINCT keyword in an aggregate function and in an SQL projection clause in the same query.

OLAP window functions

The following OLAP functions are supported by Informix. This list includes several aggregate functions that can be specified as OLAP window aggregates when the OVER clause immediately follows the function, or as ordinary aggregates that operate on the entire result set, if no OVER clause is specified:

AVG
COUNT
CUME_DIST
DENSE_RANK or DENSERANK
FIRST_VALUE
LAG
LAST_VALUE
LEAD
MAX
MIN
NTILE
PERCENT_RANK
RANGE
RANK
RATIO_TO_REPORT or RATIOTOREPORT
ROW_NUMBER or ROWNUMBER
STDEV
SUM
VARIANCE

User-defined functions

User-defined functions are not supported.

Scalar functions

The following scalar functions are supported by Informix Warehouse Accelerator:

ABS
ADD_MONTHS
CASE
CEIL
CONCAT
CURRENT
DATE
DAY
DECODE
FLOOR
LAST_DAY
LEN
LENGTH
LOWER
LPAD
LTRIM
MAX
MIN
MOD
MONTH
MONTHS_BETWEEN
NEXT_DAY
NVL
POW
POWER
QUARTER
RANGE
ROUND
RPAD
RTRIM
SQRT
SUBSTR
SUBSTRING
SYSDATE
TODAY
TRIM
TRUNC
UNITS DAY
UNITS FRACTION
UNITS HOUR
UNITS MINUTE
UNITS MONTH
UNITS SECOND
UNITS YEAR
UPPER
WEEKDAY
YEAR

Restrictions

The formatting rules that are specified when you connect to the Informix server are not necessarily applied by Informix Warehouse Accelerator when converted to character data types. Here are examples of how implicit and explicit casts to character data types are affected:
- If a query specifies an explicit cast of a DATE or DATETIME value to a character type, for example ... date_column::char(10) ..., the date value is formatted by Informix Warehouse Accelerator as yyyy-mm-dd. If this cast appears in the projection list, then the query result is displayed in this format. If it appears as a predicate, the predicate produces a match only if the counterpart is formatted in the same way.
- If a query specifies an implicit cast to a character data type, for example, by using the CONCAT function or the equivalent concatenation operator (||), Informix Warehouse Accelerator formats the output according to the default Informix server rules.
Support of the CURRENT and SYSDATE functions is limited to the same variants that are supported for the DATETIME data type, as documented in Supported data types.
Queries that use the ROUND function or the TRUNC function with the DATE data type fail when the query is sent to the Informix Warehouse Accelerator for processing. An error message about DRDA AR PREPARE is returned.
The SUBSTR and the SUBSTRING functions have the following restrictions in Informix Warehouse Accelerator:
- The start_position value must be greater than 0.
- The length value must be greater than or equal to 0.
- The sum of the start_position value and the length value must be less than or equal to the length of the source_string.
If the parameter values are outside of these boundaries, the accelerated query will cause an error message.