15.00 - Basing the Partitioning Expression on a RANGE_N Character Column - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Basing the Partitioning Expression on a RANGE_N Character Column

The RANGE_N function provides a simplified method for mapping a character value into one of a list of specified ranges, and then returning the number of that range. A range is defined by its starting and ending boundaries, inclusively.

If you do not specify an ending boundary, the range is defined by its starting boundary, inclusively, up to, but not including, the starting boundary for the next range.

The EACH clause is not supported for character test values, and Teradata Database returns an error if you specify an EACH clause in a RANGE_N function with a character test value. Each range specified for expressions character data types maps to exactly an integer range of only one value. Therefore, the number of partitions that can be specified is somewhat limited because of table header limitations and due to the increase in CPU usage to handle a large number of ranges.

You can specify an asterisk for the first starting boundary to indicate the lowest possible value, and you can specify an asterisk for the last ending boundary to indicate the highest possible value.

As with numeric RANGE_N expressions, options are provided to handle cases when the value does not map into one of the specified ranges or evaluates to UNKNOWN because of a null result, making it impossible to determine into which range the value would map.

The following rules apply to the use of the RANGE_N function for character data in addition to the rules that exist for numeric data:

  • You can specify only one test value, and it must result in an integer (BYTEINT, INTEGER, SMALLINT, BIGINT), character (CHARACTER, GRAPHIC, VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC), DATE, or TIMESTAMP data type.
  • A RANGE_N partitioning expression can specify the UPPERCASE qualifier and the following functions.
  • CHAR2HEXINT
  • INDEX
  • LOWER
  • MINDEX
  • POSITION
  • TRANSLATE
  • TRANSLATE_CHK
  • TRIM
  • UPPER
  • VARCHAR(n) CHARACTER SET GRAPHIC
  • Teradata Database aborts the request and returns an error if any of the specified ranges are defined with null boundaries, are not increasing, or overlap. Increasing order is determined using the session collation and the case sensitivity specification for the test value at the time the table is created.