Caché SQL Reference
Matches a value to a set of generated values with bitmap chunks iteration.
scalar-expression %FIND valueset [SIZE ((nn))]
||A scalar expression (most commonly the RowId field of a table) whose values are being compared with valueset.
||An object reference (oref) to a user-defined object that implements bitmap chunks iteration methods and the ContainsItem() method. This method takes a set of data values and returns a boolean when there is a match with a value in scalar-expression.
||Optional An order-of-magnitude integer (10, 100, 1000, etc.) used for query optimization.
predicate allows you to filter a result set by selecting those data values that match the values specified in valueset
, iterating through values in a sequence of bitmap chunks. This match is successful when a scalar-expression
value matches a value in valueset
. If the valueset
values do not match any of the scalar expression values, %FIND
returns the null string. This match is always performed on the logical (internal storage) data value, regardless of the display mode.
, like the other comparison conditions, is used in the WHERE
clause or the HAVING
clause of a SELECT
enables filtering of field values using an abstract, programmatically specified set of matching values. Specifically, it enables filtering of RowId field values using an abstract, programmatically specified bitmap, where valueset
behaves similar to the subscript layer of a bitmap index.
Iteration through values in a sequence of bitmap chunks is performed using the following three methods:
uses the same collation type as the column it is matched against. By default, string data types collate as not case-sensitive. If you assign a different collation type to the column, you must also apply this collation type to the %FIND substring
. Refer to %SQLUPPER
for further information on case transformation functions.
The optional %FIND
SIZE clause provides the integer nn
, which specifies an order-of-magnitude estimate of the number of values in valueset
. Caché uses this order-of-magnitude estimate to determine the optimal query plan. Specify nn
as one of the following literals: 10, 100, 1000, 10000, etc. Because nn
must be available as a constant value at compile time, it must be specified as a literal in all SQL code. Note that nesting parentheses must be specified as shown for all SQL, with the exception of Embedded SQL.
%FIND and %INSET Compared