A UDT ordering specifies how two values of the same UDT are to be compared using relational operators (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). The system also uses the ordering definition for other comparison-related operations such as groupings, orderings, sorts, and uniqueness determinations.
An ordering is associated with a UDT by means of the CREATE ORDERING statement. You must define an ordering for all UDTs that you develop, and you must complete the definition before you can use it to define the data type of any table. If you attempt to use a UDT in any way without first creating an ordering for it, the system returns an error to the requestor.
- WHERE clause with comparison operators
- [NOT] BETWEEN … AND
- [NOT] IN
- GROUP BY
- ORDER BY
- SELECT DISTINCT
- EXCEPT and MINUS set operators
- INTERSECT set operator
- UNION set operator
- COUNT (DISTINCT UDT_column)
- CREATE TABLE when the table contains a column typed with the UDT
| FOR this type of UDT … | THE ordering functionality … |
|---|---|
| distinct | is generated automatically by the system. This is not true if the source data type is a LOB. In this case, you must explicitly create an ordering for the LOB using the CREATE ORDERING statement in conjunction with an appropriate external routine. If the system-generated ordering semantics is adequate, you need not define explicit ordering functionality using the CREATE ORDERING statement. If your applications require different or richer ordering semantics, then you can specify explicit ordering functionality using CREATE ORDERING. |
| structured | must be defined explicitly using the CREATE ORDERING statement. |