RANGE_N Function Syntax | Teradata Vantage - RANGE_N Function Syntax - Advanced SQL Engine - Teradata Database

RANGE_N (test_expression BETWEEN range_expression [, range_spec ])

Syntax Elements

test_expression

An expression that results in a BYTEINT, SMALLINT, INTEGER, DATE, CHAR, VARCHAR, GRAPHIC or VARGRAPHIC data type.

RANGE_N evaluates test_expression and determines whether the result is within a range in the list of ranges. The position of the first range is one and the positions of subsequent ranges increment by one up to n, where n is the total number of ranges.

IF …	THEN …
the result of test_expression is within a range	RANGE_N returns the position of the range.
the result of test_expression is NULL	If RANGE_N does not specify one of the following: BETWEEN * AND * UNKNOWN NO RANGE OR UNKNOWN RANGE_N returns NULL. If RANGE_N specifies the range BETWEEN * AND *, RANGE_N returns 1, regardless of whether NO RANGE, NO RANGE OR UNKNOWN, or UNKNOWN is specified. If RANGE_N does not specify the range BETWEEN * AND * and If NO RANGE OR UNKNOWN is specified, RANGE_N returns n + 1. If UNKNOWN is specified and NO RANGE is not specified, RANGE_N returns n + 1. NO RANGE and UNKNOWN are specified, RANGE_N returns n + 2.
test_expression is outside all the ranges in the list	If NO RANGE or NO RANGE OR UNKNOWN is specified, RANGE_N returns n + 1. If neither NO RANGE nor NO RANGE OR UNKNOWN is specified, RANGE_N returns NULL.

range_expression

{ range_expr_1 | range_expr_2 | range_list }

range_spec

{ NO RANGE [ { OR | , } UNKNOWN ] | UNKNOWN }

range_expr_1

start_expression AND { end_expression | * } [ EACH range_size ]

range_expr_2

* AND { end_expression | * }

range_list

{ range_expr_3 | * [ AND end_expression ] }
    [, range_expr_3 [,...] ] , range_expr_1

NO RANGE

A range to handle a test_expression that does not map into any of the specified ranges.

OR UNKNOWN

NO RANGE OR UNKNOWN handles a test_expression that does not map into any of the specified ranges, or a test_expression that evaluates to NULL when RANGE_N does not specify the range BETWEEN * AND *.

UNKNOWN

Handles a test_expression that evaluates to NULL when RANGE_N does not specify the range BETWEEN * AND *.

start_expression

A literal or literal expression that defines the starting boundary of a range.

The data type of start_expression must be the same as the data type of test_expression, or must be implicitly cast to the same data type as test_expression.

If an ending boundary is not specified, the range is defined by its starting boundary (and this starting boundary is included in this range), up to but not including the starting boundary of the next range.

Use an asterisk ( * ) for the starting boundary of the first range in the list to indicate the lowest possible value (all values and NULL are greater than a starting boundary specified as an asterisk). An asterisk is compatible with any data type.

end_expression

A literal or literal expression that defines the ending boundary of a range.

The data type of end_expression must be the same as the data type of test_expression, or must be implicitly cast to the same data type as test_expression.

The last range in the list must specify an ending boundary. For all other ranges, if an ending boundary is not specified, the range is defined by its starting boundary (and this starting boundary is included in this range), up to but not including the starting boundary of the next range.

Use an asterisk ( * ) for the ending boundary of the last range in the list to indicate the highest possible value (all values and NULL are less than an ending boundary specified as an asterisk).

range_size

A literal or literal expression with a value greater than zero.

A range that specifies an EACH phrase is equivalent to a series of ranges, where the first range in the series starts at start_expression, and subsequent ranges start at start_expression + (range_size * n), where n starts at one and increments by one while start_expression + (range_size * n) is less than or equal to end_expression, or less than the next start_expression in the list of ranges.

For DATE types, the calculation of valid dates in subsequent ranges uses ADD_MONTHS instead of the + arithmetic operator.

The data type of range_size must be compatible for adding to test_expression.

If the data type of test_expression is a character type (CHAR, VARCHAR, GRAPHIC or VARGRAPHIC), you cannot specify the EACH phrase.

range_expr_3

start_expression [ AND end_expression ] [ EACH range_size ]