A function that returns a set of values from the index’s Find() method.

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

SEARCH_INDEX invokes the index-nameFind() 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.