Querying Collections
Collections may be referenced from the SQL WHERE clause, as follows:
WHERE FOR SOME %ELEMENT(collectionRef) [AS label] (predicate)
The FOR SOME %ELEMENT clause can be used for list collections and arrays that specify STORAGEDEFAULT="list". The predicate may contain one reference to the pseudo-columns %KEY, %VALUE, or both. A few examples should help to clarify how the FOR SOME %ELEMENT clause may be used. The following returns the name and the list of FavoriteColors for each person whose FavoriteColors include 'Red'.
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')
Any SQL predicate may appear after the %Value (or %Key), so for example the following is also legal syntax:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FOR SOME %ELEMENT(Sample.Person.FavoriteColors)
(%Value IN ('Red', 'Blue', 'Green'))
A list collection is considered a special case of an array collection that has sequential numeric keys 1, 2, and so on. Array collections may have arbitrary non-null keys:
FOR SOME (children) (%Key = 'betty' AND %Value > 5)
In addition to the built-in list and array collection types, generalized collections may be created by providing a BuildValueArray() class method for any property. The BuildValueArray() class method transforms the value of a property into a local array, where each subscript of the array is a %KEY and the value is the corresponding %VALUE.
In addition to simple selections on the %KEY or %VALUE, it is also possible to logically connect two collections, as in the following example:
FOR SOME %ELEMENT(flavors) AS f
(f.%VALUE IN ('Chocolate', 'Vanilla') AND
FOR SOME %ELEMENT(toppings) AS t
(t.%VALUE = 'Butterscotch' AND
f.%KEY = t.%KEY))
This example has two collections: flavors and toppings, that are positionally related through their key. The query qualifies a row that has chocolate or vanilla specified as an element of flavors, and that also has butterscotch listed as the corresponding topping, where the correspondence is established through the %KEY.
You can change this default system-wide using the CollectionProjection option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method. SET status=$SYSTEM.SQL.Util.SetOption("CollectionProjection",1,.oldval) to project a collection as a column if the collection is projected as a child table; the default is 0. Changes made to this system-wide setting takes effect for each class when that class is compiled or recompiled. You can use $SYSTEM.SQL.Util.GetOption("CollectionProjection")Opens in a new tab to return the current setting.
For information on indexing a collection, refer to Indexing Collections.
Usage Notes and Restrictions
-
FOR SOME %ELEMENT may only appear in the WHERE clause.
-
%KEY and/or %VALUE may only appear in a FOR predicate.
-
Any particular %KEY or %VALUE may be referenced only once.
-
%KEY and %VALUE may not appear in an outer join.
-
%KEY and %VALUE may not appear in a value expression (only in a predicate).