Functions Used in The Select List

TIBCO ActiveSpaces supports the use of aggregate functions, date and time functions, and string functions as a value expression or as part of a value expression.

When used as the value expression, the result of the function is used as the value for columns in the select list.

Date and Time Functions

Date and time function usage is supported for value expressions as shown in the following example: 
SELECT date(dtmcol) FROM mytable
For more information on date and time functions, see Date and Time Functions.

When using a timevalue of now with a date and time function in a value expression, now is converted to the current date and time by each node of the data grid as the query is executed and rows are found to use in the query results. Carefully analyze your use of now to ensure the query returns the results you expect. The same holds true for using the SQL variables CURRENT_DATE CURRENT_TIME and CURRENT_TIMESTAMP in a value expression.

String Functions

String functions can be used in value expressions. For example, to return the id column's value and the uppercase value of the lastname column from all rows of a table you can use the following query: 
SELECT id, UPPER(lastname) FROM mytable
For more information on the string functions supported by ActiveSpaces, see SQL String Functions.

Aggregate Functions

Aggregate functions as value expressions use the column values from multiple rows to calculate a single value. Aggregate functions are applied to column values of the rows selected to be used for the query results.

Depending on the syntax used for the SELECT statement, these rows can be the result of the GROUP BY clause or the WHERE clause. Rows from the GROUP BY clause take precedence over rows from the WHERE clause. If both the GROUP BY and WHERE clauses are omitted, values from all rows of the table are used for the aggregation.

Aggregate functions ignore NULL values. If all the values are NULL or cannot be converted to the appropriate data type for the function, the result of the function is NULL. The aggregate functions supported are listed in the following table:
Function Description
AVG(<column>) Computes the arithmetic mean of the non-NULL values in the column. The type of the result is always a Double, regardless of the data type of the underlying column. If the data type of the underlying column is String, the value is converted to a Double. If the data type of the underlying column is Opaque, the data is treated as a String and then converted to a Double.
COUNT(<column>) COUNT(*)
Computes the number of non-NULL values in the column, or the number of rows. The result is always of type Long.
Note: COUNT() is not supported.
MIN(<column>) The minimum non-NULL value in the column. The type of the result matches the type of the underlying column.
MAX(<column>) The maximum non-NULL value in the column. The type of the result matches the type of the underlying column.
SUM(<column>) Computes the sum of the non-NULL values in the column. The type of the result is Long if the data type of the underlying column is Long, otherwise it is Double. If the data type of the underlying column is String, the value is converted to a Double. If the data type of the underlying column is Opaque, the data is treated as a String and then converted to a Double.
GROUP_CONCAT( DISTINCT <column>) Concatenates the unique values for a column in the group into a comma separated list. The DISTINCT keyword is required. The order of the values in the list is not defined. The type of the result is always a String, irrespective of the data type of the underlying column. If the data type of the underlying column is Long or Double, the value is converted to a String. If the data type of the underlying column is Opaque, the data is treated as String.

For more information on the aggregate functions supported by ActiveSpaces, see Aggregate Functions.

Related concepts