Using scalar and aggregate functions

The query engine includes a set of built-in scalar and aggregate functions that can be used in expressions within data models and reports.

A scalar function produces an output for each row of input, for example, taking the ABS of a column or expression. An aggregate function accepts values from multiple rows and produces an output, for example, taking the maximum of a column or expression. As a query is run, those expressions can be transformed into the equivalent SQL expressions that are supported by a data source. Part of this process is ensuring that appropriate grouping or window specifications are generated for expressions that require a grouping clause or a window specification.

An expression can include reference to a function that is not a built-in function, but is supported by the data source that the query references. By default, the query service assumes that it would be a scalar function. As SQL is generated for the data source, the query service doesn’t ensure that an appropriate group by or window specification, which might be necessary, is included for the function.

For example, a database includes a user-defined aggregate myaggregate, which accepts a set of input row values and produces a single summary that is similar to standard aggregates, such as sum, avg or min. The Cognos® Analytics query might include other items that the query service knows require a group by clause. Hence, it accepts the reference to myaggregate. If this reference is not accepted, the data source might reject the SQL statement because it doesn’t include the expected group by or window specification.

For more information, see Creating calculations, and Using relational calculations..

Qualified function names

When a data source parses an SQL statement that includes a reference to a user-defined function, the data source attempts to resolve the reference to that function. Similar to confirming the reference to tables or views, a search mechanism is used to resolve a non-qualified reference. For example, the data source might attempt to look for myaggregate in the current catalog or schema context of the session, or follow a form of search path. The query service doesn’t allow a reference to a function to be prefixed by a catalog or schema name. For example, catalogA.schemaB.myaggregate() is not accepted. For more information about search paths for functions, refer to the vendors SQL reference documentation.

If the data source requires the function to be qualified in the generated SQL, the function might be either imported into Framework Manager or prefixed by additional syntax. Importing functions into Framework Manager creates function objects that are associated to a data source object in the model. A data source object has an optional catalog and schema name property that is used to qualify names to tables, views, or functions. If the function is used in an expression, the qualification from the associated data source object is used.

Typically, functions are not imported from the database into a Framework Manager model. However, if functions are imported, they are assumed to be scalar functions, and not aggregate or tabular. Instead, qualify the function with the appropriate catalog or schema name, such as mycatalog.myschema.myfunction( ... ).

Aggregate or windowed functions

A reference to an aggregate or window function in a data source must be prefixed with the aggregate keyword. For example, aggregate:myaggregate ( [c1] ).

An optional over clause can be included that specifies how to partition the data. For example, aggregate:myaggregate( [c1] ) over ( [c2] ).

The over clause doesn’t support other ISO-SQL constructs, including ORDER BY, WITHIN GROUP, ROWS, or RANGE.

Currently, the query service might not parse an aggregate function that expects no input parameters. For example, aggregate:myaggregate() over ( [c2] ).

Using quotation marks

A data source might use case-sensitive names. For example, HELLO, hello, and Hello might be considered as three different objects. In situations when the function name is case-sensitive, conflicts with vendor's reserved keywords, or includes special characters, the name must be enclosed in double quotation marks ("). The query service then translates the characters to the required delimited identifier characters that are used by the database. For example, "myfunction"( [c1] ).

Typically, the user-defined function names differ from the SQL standard scalar and aggregate function names, such as ABS or SUM. If a user defines an aggregate in the data source with a name that already exists in the data source as a function name in the query service, the user-defined aggregate must be enclosed in quotation marks ("). For example, the aggregate "COUNT".