Comparing Data

The compare() function is an SQL-invoked function that sorts the target data type. The database server uses the compare() function in the CREATE INDEX statement and to execute the following components of the SELECT statement:

• The ORDER BY clause
• The UNIQUE and DISTINCT keywords
• The UNION keyword
• The BETWEEN operator

For more information on the SELECT statement, see the IBM Informix: Guide to SQL Syntax.

If you define a compare() function, you must also define the greaterthan(), lessthan(), equal or other functions that use the compare function.

For the database server to be able to sort an opaque type, you must define a compare() function that handles the opaque type. The compare() function must follow these rules:

1. The name of the function must be compare(). However, the name is not case sensitive; the compare() function is the same as the Compare() function.
2. The function must accept two arguments, each of the data types to be compared.
3. The function must return an integer value to indicate the result of the comparison, as follows:
   • <0 to indicate that the first argument is less than (<) the second argument
   • 0 to indicate that the two arguments are equal (=)
   • >0 to indicate that the first argument is greater than (>) the second argument

The compare() function is the support function for the built-in secondary-access method, B-tree. For more information on the built-in secondary-access method, see Generic B-Tree Index. For more information on how to customize a secondary-access method for an opaque data type, see Using Operator Classes.