16.20 - Evaluation - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.