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.
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'
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.
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.
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
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.
GemFire provides several built-in functions for evaluating or filtering data returned from a query. They include the following:
|ELEMENT(expr)||Extracts a single element from a collection or array. This function throws a
|IS_DEFINED(expr)||Returns TRUE if the expression does not evaluate to UNDEFINED.||
|IS_UNDEFINED (expr)||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.||
|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.|
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.
SELECT COUNT(*) FROM /exampleRegion p, p.positions.values pos WHERE p.ID > 0 AND pos.secId 'IBM'
The following COUNT query uses the DISTINCT keyword and eliminates duplicates from the number of results.
SELECT DISTINCT COUNT(*) FROM /exampleRegion p, p.positions.values pos WHERE p.ID > 0 OR p.status = 'active' OR pos.secId OR pos.secId = 'IBM'