Comparison function for opaque data types

The compare() function is an SQL-invoked function that sorts the target data type. The database server uses the compare() function to execute the following clauses and keywords of the SELECT statement:
  • The ORDER BY clause
  • The UNIQUE and DISTINCT keywords
  • The UNION keyword

The database server also uses the compare() function to evaluate the BETWEEN operator in the condition of an SQL statement. For more information about conditional clauses, see the Condition segment in the HCL OneDB™ Guide to SQL: Syntax.

The database server provides compare() functions that handle the built-in data types. For the database server to be able to sort an opaque data type, you must define a compare() function to handle this opaque data type.

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

If you overload the compare() function, make sure you follow these rules:
  1. The name of the function must be compare(). 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 precedes the second argument
    • 0 to indicate that the two arguments are the same
    • >0 to indicate that the first argument comes after the second argument

The compare() function is also the support function for the default operator class of the B-tree secondary-access method. For more information, see Generic B-tree index.