Pivotal GemFire® v8.1

FROM Clause

FROM Clause

The FROM clause establishes collections of objects that are iterated over by the remainder of the query.

The attributes of the objects in these collections are added to the name space scope for the remainder of the FROM clause as well as for the WHERE clause and the SELECT projection list.

Each FROM clause expression must evaluate to a collection. The expression /portfolios.keySet is valid because it evaluates to a Collection, but /, which evaluates to a String , causes an exception to be thrown.

Like the SQL query, which iterates over the tables named in its FROM clause, the OQL query iterates over the Collections established in its FROM clause.

In the following query, positions.values evaluates to a Collection because positions is a Map, and the method values on Map returns a Collection.

IMPORT javaobject.Position;
FROM /portfolios, positions.values posnVal TYPE Position
WHERE posnVal.qty > 1000.00

Every expression in the FROM clause must evaluate to a Collection. For a Map, the values method returns a Collection.

If positions were a List instead of a Map , this query could be used to retrieve the data:

IMPORT javaobject.Position;
FROM /portfolios, positions posnVal TYPE Position
WHERE posnVal.qty >= 1000.00

A List is a Collection, so you can access it directly or through its toArray method.

For each object type accessed in your FROM clause, use the method that returns a Collection for that object.

Each expression in the FROM clause can be any expression that evaluates to a Collection. An expression in the FROM clause is typically a path expression that resolves to a region in the cache so that the values in the region become the collection of objects to filter.

For example, this is a simple SELECT statement that evaluates to a set of all the entry value objects of the region /portfolios with active status. The collection of entry values provided by the FROM clause is traversed by the WHERE clause, which accesses each element’s status attribute for comparison.

SELECT DISTINCT * FROM /portfolios WHERE status = 'active'

If the FROM clause has only one expression in it, the result of the clause is the single collection that the expression evaluates to. If the clause has more than one expression in it, the result is a collection of structs that contain a member for each of those collection expressions. For example, if the FROM clause contains three expressions that evaluate to collections C1, C2, and C3, the FROM clause generates a set of struct(x1, x2, x3) where x1, x2, and x3 represent nested iterations over the collections specified.

If the collections are independent of each other, this struct represents their cartesian product.

In this query, the FROM clause produces a struct of portfolio and position pairs to be iterated. Each element in the struct contains the portfolio and one of its contained positions.

IMPORT javaobject.Position;
SELECT DISTINCT "type" FROM /portfolios, positions TYPE Position
WHERE qty > 1000.00

To understand the effects of FROM expressions on query scope, see Drilling Down for Modifying Query Scope.