This topic explains how to use the SELECT statement in queries in VMware Tanzu GemFire.

The SELECT statement allows you to filter data from the collection of objects 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 secId FROM /exampleRegion, positions.values TYPE Position 
WHERE status = 'active'

Returns a Collection of struct<type: String, positions: map> for the active portfolios. The second field of the struct is a Map ( jav.utils.Map ) object, which contains the positions map as the value:

SELECT "type", positions FROM /exampleRegion 
WHERE status = 'active'

Returns a Collection of struct<portfolios: Portfolio, values: Position> for the active portfolios:

SELECT * FROM /exampleRegion, positions.values 
TYPE Position WHERE status = 'active'

Returns a Collection of struct<pflo: Portfolio, posn: Position> for the active portfolios:

SELECT * FROM /exampleRegion portfolio, positions positions 
TYPE Position WHERE portfolio.status = 'active'

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:

  1. 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

  2. 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:

  1. If a field is specified explicitly using the fieldname:expression syntax, the fieldname is used.
  2. 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.
  3. If the field is associated with a region or attribute path, the last attribute name in the path is used.
  4. If names cannot be decided based on these rules, arbitrary unique names are generated by the query processor.

DISTINCT

To limit the results set to unique rows, use the DISTINCT keyword. For example:

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

Tanzu 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. 
ELEMENT(SELECT DISTINCT * 
 FROM /exampleRegion 
 WHERE id = 'XYZ-1').status = 'active'
IS_DEFINED(expr) Returns TRUE if the expression does not evaluate to UNDEFINED. Inequality queries include undefined values in their query results. With the IS_DEFINED function, you can limit results to only those elements with defined values. 
IS_DEFINED(SELECT DISTINCT * 
FROM /exampleRegion p 
WHERE p.status = 'active')
IS_UNDEFINED (expr) Returns TRUE if the expression evaluates to UNDEFINED. With the exception of inequality queries, most queries do not include undefined values in their query results. The IS_UNDEFINED function allows undefined values to be included, so you can identify elements 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 Date. 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.  
check-circle-line exclamation-circle-line close-line
Scroll to top icon