SELECT Statement

The SELECT statement allows you to filter data from the collection of object(s) returned by a WHERE search operation. The projection list is either specified as * or as a comma delimited list of expressions.

For *, the interim results of the WHERE clause are returned from the query.

Examples:

Query all objects from the region using *. Returns the Collection of portfolios (The exampleRegion contains Portfolio as values).

SELECT * FROM /exampleRegion

Query secIds from positions. Returns the Collection of secIds from the positions of active portfolios:

SELECT Statement Results

The result of a SELECT statement is either UNDEFINED or is a Collection that implements the SelectResults interface.

The SelectResults returned from the SELECT statement is either:

A collection of objects, returned for these two cases:

When only one expression is specified by the projection list and that expression is not explicitly specified using the fieldname:expression syntax

When the SELECT list is * and a single collection is specified in the FROM clause

A collection of Structs that contains the objects

When a struct is returned, the name of each field in the struct is determined following this order of preference:

If a field is specified explicitly using the fieldname:expression syntax, the fieldname is used.

If the SELECT projection list is * and an explicit iterator expression is used in the FROM clause, the iterator variable name is used as the field name.

If the field is associated with a region or attribute path, the last attribute name in the path is used.

If names cannot be decided based on these rules, arbitrary unique names are generated by the query processor.

DISTINCT

Use the DISTINCT keyword if you want to limit the results set to unique rows. Note that in the current version of GemFire you are no longer required to use the DISTINCT keyword in your SELECT statement.

SELECT DISTINCT * FROM /exampleRegion

Note:
If you are using DISTINCT queries, you must implement the equals and hashCode methods for the objects that you query.

LIMIT

You can use the LIMIT keyword at the end of the query string to limit the number of values returned.

For example, this query returns at most 10 values:

SELECT * FROM /exampleRegion LIMIT 10

ORDER BY

You can order your query results in ascending or descending order by using the ORDER BY clause. You must use DISTINCT when you write ORDER BY queries.

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID

The following query sorts the results in ascending order:

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID asc

The following query sorts the results in descending order:

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID desc

Note:
If you are using ORDER BY queries, you must implement the equals and hashCode methods for the objects that you query.

Preset Query Functions

GemFire provides several built-in functions for evaluating or filtering data returned from a query. They include the following:

Function

Description

Example

ELEMENT(expr)

Extracts a single element from a collection or array. This function throws a FunctionDomainException if the argument is not a collection or array with exactly one element.

Returns TRUE if the expression evaluates to UNDEFINED. In most queries, undefined values are not included in the query results. The IS_UNDEFINED function allows undefined values to be included, so you can identify element with undefined values.

SELECT DISTINCT *
FROM /exampleRegion p
WHERE IS_UNDEFINED(p.status)

NVL(expr1, expr2)

Returns expr2 if expr1 is null. The expressions can be query parameters (bind arguments), path expressions, or literals.

TO_DATE(date_str, format_str)

Returns a Java Data class object. The arguments must be String S with date_str representing the date and format_str representing the format used by date_str. The format_str you provide is parsed using java.text.SimpleDateFormat.

COUNT

The COUNT keyword returns the number of results that match the query selection conditions specified in the WHERE clause. Using COUNT allows you to determine the size of a results set. The COUNT statement always returns an integer as its result.

The following queries are example COUNT queries that return region entries:

SELECT COUNT(*) FROM /exampleRegion

SELECT COUNT(*) FROM /exampleRegion WHERE ID > 0

SELECT COUNT(*) FROM /exampleRegion WHERE ID > 0 LIMIT 50

SELECT COUNT(*) FROM /exampleRegion
WHERE ID >0 AND status LIKE 'act%'

SELECT COUNT(*) FROM /exampleRegion
WHERE ID IN SET(1,2,3,4,5)

The following COUNT query returns the total number of StructTypes that match the query’s selection criteria.