Supported functions and expressions

Aggregate functions and expressions

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

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.