Caché SQL Reference
FOR SOME %ELEMENT
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Matches list element values or the number of list elements with a predicate.
Synopsis
FOR SOME %ELEMENT(field) [[AS] e-alias] (predicate)
Arguments
field A scalar expression (most commonly a data column) whose elements are being compared with predicate.
AS e-alias Optional — An element alias used to qualify %KEY or %VALUE within the predicate. Commonly, this alias is used when the predicate contains a nested FOR SOME %ELEMENT condition. The alias must be a valid identifier. For further details see the “Identifiers” chapter of Using Caché SQL. The AS keyword is optional.
(predicate) A predicate condition, enclosed in parentheses. Within this condition use %VALUE and/or %KEY to determine what the condition is matching. %VALUE matches the element value (%VALUE=’Red’). %KEY matches the minimum number of elements (%KEY=2). Within this condition, %VALUE and %KEY may be optionally qualified if you have specified an e-alias. This predicate can consist of multiple condition expressions with AND and OR logical operators.
Description
The FOR SOME %ELEMENT predicate matches the list elements in field with the specified predicate. The SOME keyword specifies that at least one of the elements in the field must satisfy the specified predicate clause.
The predicate clause must contain either the %VALUE or the %KEY keyword, followed by a predicate condition. These keywords are not case sensitive.
The use of %VALUE and %KEY is explained in the following examples:
FOR SOME %ELEMENT cannot be used to match a field that is NULL.
The predicate clause can use any predicate condition, not just the equality condition. The following are some examples of predicate clauses:
(%VALUE='Red')
(%VALUE > 21)
(%VALUE %STARTSWITH 'R')
(%VALUE [ 'e')
(%VALUE IN ('Red','Blue')
(%VALUE IS NOT NULL)
(%KEY=3)
(%KEY > 1)
(%KEY IS NOT NULL)
For performance reasons, the predicate %STARTSWITH 'abc' is preferable to the equivalent predicate LIKE 'abc%'.
You can specify multiple predicate conditions using AND, OR, and NOT logical operators. Caché applies the combined predicate conditions to each element. Therefore, it is not meaningful to apply two %VALUE or two %KEY predicates using an AND test.
For example, using FOR SOME %ELEMENT to match a field containing the values Red, Green, Red Green, Black Red, Green Yellow Red, Green Black, Yellow, or Black Yellow:
FOR SOME %ELEMENT is a collection predicate. It can be used in most contexts where a predicate condition can be specified, as described in the Overview of Predicates page of this manual. It is subject to the following restrictions:
Collection Index
An important use of FOR SOME %ELEMENT is to select elements using a collection index. If the appropriate KEYS or ELEMENTS index is defined for field, Caché uses this index rather than directly referencing the field value elements.
If the following collection index is defined:
 INDEX fcIDX1 ON FavoriteColors(ELEMENTS);
The following query uses this index:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red')
If the following collection index is defined:
 INDEX fcIDX2 ON FavoriteColors(KEYS) [ Type = bitmap ];
The following query uses this index:
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%KEY=2)
For further details on FOR SOME %ELEMENT with collection indices, refer to Collection Indexing and Querying Collections through SQL in the “Querying the Database” chapter of Using Caché SQL.
Examples
The following example uses FOR SOME %ELEMENT to return those rows where the FavoriteColors list contains the element 'Red':
SELECT Name,FavoriteColors
FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red')
 
In the following example, the %VALUE predicate contains an IN statement specifying a comma-separated list. This example returns those rows where the FavoriteColors list contains either the element 'Red' or the element 'Blue' (or both):
SELECT Name,FavoriteColors
FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE IN ('Red','Blue'))
 
The following example uses a predicate clause with two Contains operators ([). It returns those rows where the FavoriteColors list has an element that contains a lowercase 'l' and a lowercase 'e' (the contains operator is case-sensitive). In this case, the elements 'Blue', 'Yellow', and 'Purple':
SELECT Name,FavoriteColors AS Preferences
FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors) AS fc (fc.%VALUE [ 'l' AND fc.%VALUE [ 'e')
 
This example also demonstrates how an element alias (e-alias) is used.
The following Dynamic SQL example uses %KEY to return rows based on the number of elements in FavoriteColors. The first %Execute() sets %KEY=1, returning all rows that have one or more FavoriteColors elements. The second %Execute() sets %KEY=2, returning all rows that have two or more FavoriteColors elements:
  ZNSPACE "SAMPLES"
  SET q1 = "SELECT %ID,Name,FavoriteColors FROM Sample.Person "
  SET q2 = "WHERE FOR SOME %ELEMENT(FavoriteColors) (%KEY=?)"
  SET myquery = q1_q2
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(1)
  DO rset.%Display()
  WRITE !,"End of data %KEY 1",!!
  SET rset = tStatement.%Execute(2)
  DO rset.%Display()
  WRITE !,"End of data %KEY 2"
 
See Also