SQL invocation of AEs

An AE scalar function can be used wherever normal SQL scalar functions appear, such as the NPS system built-in scalar functions.

A scalar function returns one value per input, as shown here:

SELECT myScalarFunction('+', 1, 2, 3)

A table function can be used almost anywhere SQL allows a table. For more information, see the IBM Netezza User-Defined Functions Developer’s Guide. The table function returns a table that can contain a number of rows less than, equal to, or greater than the number of input rows.

SELECT * FROM TABLE WITH FINAL(myTableFunction('+', 1, 2, 3))

An aggregate function can be used where SQL allows an aggregate function. An aggregate function returns a single value per group or window. A grand aggregate returns a single result for all the data.

SELECT category, myAggregate(f1, f2, f3, f4) FROM mytable GROUP BY category

Arguments to the SQL function become the input to the AE. The output of the AE, in turn, becomes the results of the SQL function. For a language like C, a data connection is represented as an opaque handle to an API. For a language like C++ or Java, a connection is represented as a class object.