15.00 - Function of UDT Ordering Maps - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Function of UDT Ordering Maps

A UDT ordering specifies how two values of the same UDT are to be compared using relational operators (see SQL Functions, Operators, Expressions, and Predicates). 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.

There are a number of SQL operations that depend on an ordering definition to complete. For instance, a UDT must have an ordering to be used in any of the following operations.

  • 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.