Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.0

# Functions Used in The Select List

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

The following example shows the date and time function use as a value expression:

    SELECT date(dtmcol) FROM mytable

For more information on date and time functions, see Date and Time Functions.

When using a time value 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 run and rows are found to use in the query results. Carefully analyze your use of `now` to ensure that the query returns the results that 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 value of the `id` column 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 the 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: 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 about the aggregate functions supported by ActiveSpaces, see Aggregate Functions.

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveSpaces® - Enterprise Edition

Copyright © 2009-2025. Cloud Software Group, Inc. All Rights Reserved.