SEARCH_INDEX - Caché SQL Reference - Caché & Ensemble 2018.1

SEARCH_INDEX([[schema-name.]table-name.]index-name[,findparam[,...])

table-name	Optional — The name of an existing table for which index-name is defined. Cannot be a view. The table’s schema_name is optional. If omitted, all tables specified in the FROM clause are searched.
index-name	The index to be searched. The SqlName of the index map of an existing index.
findparam	Optional — An parameter or a comma-separated list of parameters to be passed to the index’s Find() method.

SEARCH_INDEX invokes the index-name Find() method and returns a set of values. You can optionally pass parameters to this Find() method. For example, SEARCH_INDEX(Sample.Person.NameIDX) invokes the Sample.Person.NameIDXFind() method.

SEARCH_INDEX can be used with the %FIND predicate in a WHERE clause to supply the oref of an object that provides an abstract representation encapsulating a set of values. These values are commonly row IDs returned by a method called at query run time. SEARCH_INDEX invokes the index’s Find() method to return this oref. This usage is shown in the following example:

SELECT Name FROM Sample.Person AS P
WHERE P.Name %FIND SEARCH_INDEX(Sample.Person.NameIDX)

The index must be found within the tables referenced by the SQL statement. An SQLCODE -151 error is generated if the specified index-name does not exist within the tables used by the SQL statement. An SQLCODE -152 error is generated if the specified index-name is not fully qualified, and is therefore ambiguous (could refer to more than one existing index) within the tables used by the SQL statement.

If the index exists, but it has no corresponding Find() method, a runtime SQLCODE -149 error is generated “SQL Function encountered an error”, the error being <METHOD DOES NOT EXIST>.

For further details on the use of SEARCH_INDEX, refer to the iFind Search Tool chapter of Using iKnow.