Range Queries
XLeratorDB functions install as aggregate functions and scalar functions.
Aggregate functions operate on a collection of values but return a single, summarizing value. Some XLeratorDB aggregate function include: FVSCHEDULE, GCD, LCM, PRODUCT, SUM and SUMSQ. Most other XLeratorDB functions are scalar.
Scalar functions operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid.
In XLeratorDB there are some functions that look like they might be aggregate functions but have been implemented as scalar functions. For example, the NPV function operates on a collection of values and returns a single, summarizing value. However, the limitations of the SQL CLR prevented implementation of these functions as aggregate functions. We have labeled the arguments to these functions that define a set of input data as Range Queries.
Some XLeratorDB functions that use Range Queries are: IRR, MIRR, MULTINOMIAL, NPV, SERIESSUM, XIRR, and XNPV. Each of these functions, in turn has a complementary function that ends in ‘_q’: IRR_q, MIRR_q, MULTINOMIAL_q, NPV_q, SERIESSUM_q, XIRR_q, and XNPV_q.
Range queries allow you to specify the data that you want included in the result set of the function independently of the WHERE clause associated with the T-SQL statement. In the first set of functions, you specify the table, the column or columns to be processed, and the value to group the results on. The second set of functions allows you to identify more specifically the data to be used by incorporating a SELECT statement into the function as a string argument. The function then traps that SELECT statement, executes it internally and processes the result.
For specific examples, see the documentation for each function.