Aggregates, Expressions, and Functions
Relational database engines support the notion of selecting more than simple column references. There are aggregate functions such as MIN, MAX, SUM and AVG, which apply to entire columns that may be grouped with a GROUP BY clause. There are also arithmetic and string operations that apply to scalar values, and there are often built-in functions such as USER or DATE.
Using an
RWDBSelector to select arbitrary expressions is simple: just insert an
RWDBExpr representing the desired expression. For example, to select the sum of two columns, use this type of statement:
select << table1["numericColumn1"] + table1["numericColumn2"];
Functions and aggregates are handled with a uniform functional notation by providing predefined functions that accept an expression (see
Table 7). The DB Interface Module then uses
RWDBPhraseBook anonymously to hide database-dependent details:
select << rwdbMax(table1["column1"]); //1
select << rwdbUpper(table1["column2"]); //2
select << rwdbSystemDateTime(); //3
Line
//1 represents an SQL aggregate
MAX, line
//2 represents a function that converts its argument to upper case, and
//3 represents a function that returns the current date and time as known to the database.
Table 7 summarizes the predefined functions of the DB Interface Module. See the entry for
RWDBExpr in the
DB Interface Module Reference Guide for the syntax used in these functions, and the Access Module guides for the database-dependent expansion of functions. Note that there is also an extensible method that allows you to define your own functions.
Table 7 – Predefined functions of the DB Interface Module
Function | Description |
|---|
rwdbAvg | The AVG aggregate |
rwdbCast | Type conversion |
rwdbCharLength | Length of character data |
rwdbCount | The COUNT aggregate |
rwdbCountDistinct | The COUNT DISTINCT aggregate |
rwdbCurrentUser | The application's database user name |
rwdbExists | The SQL EXISTS function |
rwdbLower | Converts a string to lower case |
rwdbMax | The MAX aggregate |
rwdbMin | The MIN aggregate |
rwdbName | Assigns a name to a column |
rwdbPosition | Finds the index in one string of another string |
rwdbSessionUser | The application's database login name |
rwdbSubString | Extracts a substring from a string |
rwdbSum | The SUM aggregate |
rwdbSystemDateTime | The current date and time, according to the database |
rwdbSystemUser | The application's login name |
rwdbTrimBoth | Trims both leading and trailing characters from a string. Default character is blank. |
rwdbTrimLeading | Trims leading characters from a string. Default character is blank. |
rwdbTrimTrailing | Trims trailing characters from a string. Default character is blank. |
rwdbUpper | Converts a string to upper case |
