15.00 - CASE_N - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

CASE_N

Purpose  

Evaluates a list of conditions and returns the position of the first condition that evaluates to TRUE, provided that no prior condition in the list evaluates to UNKNOWN.

Syntax  

where:

 

Syntax element …

Specifies …

conditional_expression

a conditional expression or comma-separated list of condition expressions to evaluate.

A conditional expression must evaluate to TRUE, FALSE, or UNKNOWN.

NO CASE

an optional condition that evaluates to TRUE if every conditional_expression in the list evaluates to FALSE.

OR UNKNOWN

an optional condition to use with NO CASE.

The NO CASE OR UNKNOWN condition evaluates to TRUE if every conditional_expression in the list evaluates to FALSE, or if a conditional_expression evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE.

UNKNOWN

an optional condition that evaluates to TRUE if a conditional_expression evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Evaluation

CASE_N evaluates conditional_expressions from left to right until a condition evaluates to TRUE or UNKNOWN, or until every condition evaluates to FALSE. The position of the first conditional_expression is one and the positions of subsequent conditions increment by one up to n, where n is the total number of conditional expressions.

 

IF …

THEN …

a conditional_expression evaluates to TRUE, and all prior conditions evaluate to FALSE

CASE_N returns the position of the conditional_expression.

a conditional_expression evaluates to UNKNOWN, and all prior conditions evaluate to FALSE

 

If NO CASE OR UNKNOWN is specified, CASE_N returns n + 1.

If UNKNOWN is specified and NO CASE is not specified, CASE_N returns n + 1.

If NO CASE and UNKNOWN are specified, CASE_N returns n + 2.

If neither UNKNOWN nor NO CASE OR UNKNOWN is specified, CASE_N returns NULL.

every conditional_expression evaluates to FALSE

 

If NO CASE or NO CASE OR UNKNOWN is specified, CASE_N returns n + 1.

If neither NO CASE nor NO CASE OR UNKNOWN is specified, CASE_N returns NULL

Result Type and Attributes

The data type, format, and title for CASE_N are as follows.

 

Data Type

Format

Title

INTEGER

Default format for INTEGER

<CASE_N function>

For information on default data type formats, see SQL Data Types and Literals.

Using CASE_N to Define Partitioned Primary Indexes

The primary index for a table or join index controls the distribution and retrieval of the data for that table or join index across the AMPs. If the primary index is a partitioned primary index (PPI), the data can be assigned to user-defined partitions on the AMPs.

To define a primary index for a table or join index, you specify the PRIMARY INDEX phrase in the CREATE TABLE or CREATE JOIN INDEX data definition statement. To define a partitioned primary index, you include the PARTITION BY phrase when you define the primary index.

The PARTITION BY phrase requires one or more partitioning expressions that determine the partition assignment of a row. You can use CASE_N to construct a partitioning expression such that a row with any value or NULL for the partitioning columns is assigned to some partition.

You can also use RANGE_N to construct a partitioning expression. For more information, see “RANGE_N” on page 153.

If the PARTITION BY phrase specifies a list of partitioning expressions, the PPI is a multilevel PPI, where each partition for a level is subpartitioned according to the next partitioning expression in the list. Unlike the partitioning expression for a single-level PPI, which can consist of any valid SQL expression (with some exceptions), each expression in the list of partitioning expressions for a multilevel PPI must be a CASE_N or RANGE_N function.

You cannot ADD or DROP partitioning expressions that are based on a CASE_N function. To modify a partitioning expression that is based on a CASE_N function, you must use the ALTER TABLE statement with the MODIFY PRIMARY INDEX option to redefine the entire PARTITION BY clause, and the table must be empty. For more information, see “ALTER TABLE” in SQL Data Definition Language.

Using CASE_N with CURRENT_DATE or CURRENT_TIMESTAMP in a PPI

You can define a partitioning expression that uses CASE_N with the built-in functions CURRENT_DATE or CURRENT_TIMESTAMP. Subsequently, you can use the ALTER TABLE TO CURRENT statement to re-partition the table data using a newly resolved current date or timestamp. For more information, see “Rules and Guidelines for Optimizing the Reconciliation of CASE_N PPI Expressions Based On Updatable Current Date and Updatable Current Timestamp” in SQL Data Definition Language Detailed Topics.

Using CASE_N with Character Comparison

You can specify conditional expressions in the CASE_N function that compare CHAR, VARCHAR, GRAPHIC or VARGRAPHIC data types. The following usage rules apply:

  • A CASE_N partitioning expression can use character or graphic comparison except when the comparison involves KANJI1 or KANJISJIS columns or literal expressions.
  • A CASE_N partitioning expression can use the UPPERCASE qualifier and the following functions: LOWER, UPPER, TRANSLATE, TRIM, VARGRAPHIC, INDEX, MINDEX, POSITION, TRANSLATE_CHK, CHAR2HEXINT.
  • Any string literal referenced within a CASE_N expression must be less than 31,000 bytes.
  • The order of character data used in evaluating the conditional expressions in a CASE_N function is determined by the session collation and case specificity of the expression.
  • If the expression is not part of a PPI, the current session collation is used.
  • If the expression is part of a PPI, evaluation is done using the session collation that was in effect when the table or join index was created, or when the partitioning was modified using the ALTER TABLE statement.
  • The case specificity of column references and literals is determined based on the session default, or an explicit CAST, or a specification in the CREATE TABLE statement when the table was created. The column can be explicitly assigned to be CASESPECIFIC or NOT CASESPECIFIC, and literal expressions can be CAST with these qualifiers.
  • If not explicitly specified, the default of NOT CASESPECIFIC is used if Teradata session transaction semantics are in effect. If ANSI session transaction semantics are in effect, the default is CASESPECIFIC.

    For example, if a conditional expression is a combination of NOT CASESPECIFIC expressions and a literal with no case specific qualifier (CASESPECIFIC, NOT CASESPECIFIC), the case specificity will be case specific in ANSI mode sessions and not case specific in Teradata mode sessions.

    Note: All character string comparisons involving graphic data are case specific.

  • In character comparison operations (=, <, >, <=, >=, <>, BETWEEN, LIKE), if a string literal is shorter than the column data to which it is compared, the string literal is treated as if it is padded with a pad character specific to the character set (for example, a <space> character).
  • Note that the pad character might not collate to the lowest code point in the collation. For a literal of length n, if the column value being compared precisely matches the literal for the first n characters, but contains a character that collates less than the pad character at position n+1, then the column value will collate less than the string literal. See “Example 9” on page 132.

    Restrictions

    If CASE_N is used in a PARTITION BY phrase, it:

  • Can specify a maximum of 65533 conditions (unless it is part of a larger partitioning expression)
  • Must not contain the system-derived columns PARTITION or PARTITION#L1 through PARTITION#L15
  • Must not use Period data types, but can use the following:
  • BEGIN bound function for which input is a Period data type column and not a Period expression.
  • END bound function for which input is a Period data type column and not a Period expression.
  • IS [NOT] UNTIL_CHANGED.
  • IS [NOT] UNTIL_CLOSED.
  • If CASE_N is used in a partitioning expression for a multilevel PPI, it must define at least two partitions.

    Note that partition elimination for queries is often limited to literal or using value equality conditions on the partitioning columns, and the Optimizer may not eliminate some partitions when it possibly could. Also, evaluating a complex CASE_N may be costly in terms of CPU cycles and the overhead of CASE_N may cause the table header to be excessively large.

    Example  

    Here is an example that uses CASE_N and the value of the totalorders column to define the partition to which a row is assigned:

       CREATE TABLE orders 
        (storeid INTEGER NOT NULL
        ,productid INTEGER NOT NULL
        ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL
        ,totalorders INTEGER)
        PRIMARY INDEX (storeid, productid) 
         PARTITION BY CASE_N(totalorders < 100, totalorders < 1000,
                             NO CASE, UNKNOWN);

    In the example, CASE_N specifies four partitions to which a row can be assigned, based on the value of the totalorders column.

     

    Partition Number

    Condition

    1

    The value of the totalorders column is less than 100.

    2

    The value of the totalorders column is less than 1000, but greater than or equal to 100.

    3

    The value of the totalorders column is greater than or equal to 1000.

    4

    The totalorders column is NULL.

    Example  

    Here is an example that modifies “Example 1” to use CASE_N in a list of partitioning expressions that define a multilevel PPI:

       CREATE TABLE orders 
        (storeid INTEGER NOT NULL
        ,productid INTEGER NOT NULL
        ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL
        ,totalorders INTEGER NOT NULL)
        PRIMARY INDEX (storeid, productid) 
         PARTITION BY (CASE_N(totalorders < 100, totalorders < 1000,
                              NO CASE)
                      ,CASE_N(orderdate <= '2005-12-31', NO CASE) );
     

    The example defines six partitions to which a row can be assigned. The first CASE_N expression defines three partitions based on the value of the totalorders column. The second CASE_N expression subdivides each of the three partitions into two partitions based on the value of the orderdate column.

     

    Level 1 Partition Number

    Level 2 Partition Number

    Condition

    1

     

     

    1

    The value of the totalorders column is less than 100 and the value of the orderdate column is less than or equal to '2005‑12‑31'.

    2

    The value of the totalorders column is less than 100 and the value of the orderdate column is greater than '2005‑12‑31'.

    2

     

     

     

     

    1

    The value of the totalorders column is less than 1000 but greater than or equal to 100, and the value of the orderdate column is less than or equal to '2005‑12‑31'.

    2

    The value of the totalorders column is less than 1000 but greater than or equal to 100, and the value of the orderdate column is greater than '2005‑12‑31'.

    3

     

     

    1

    The value of the totalorders column is greater than or equal to 1000 and the value of the orderdate column is less than or equal to '2005‑12‑31'.

    2

    The value of the totalorders column is greater than or equal to 1000 and the value of the orderdate column is greater than '2005‑12‑31'.

    Example  

    The following example shows the count of rows in each partition if the orders table were to be partitioned using the CASE_N expression.

       CREATE TABLE orders 
        (orderkey INTEGER NOT NULL
        ,custkey INTEGER
        ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL)
        PRIMARY INDEX (orderkey);
       
       INSERT INTO orders (1, 1, '1996-01-01');
       INSERT INTO orders (2, 1, '1997-04-01');
       

    The CASE_N expression in the following SELECT statement specifies three conditional expressions and the NO CASE condition.

       SELECT COUNT(*), 
              CASE_N(orderdate >= '1996-01-01' AND
                     orderdate <= '1996-12-31' AND
                     custkey <> 999999,
                     orderdate >= '1997-01-01' AND
                     orderdate <= '1997-12-31' AND
                     custkey <> 999999,
                     orderdate >= '1998-01-01' AND
                     orderdate <= '1998-12-31' AND
                     custkey <> 999999,
                     NO CASE
              ) AS Partition_Number
       FROM orders 
       GROUP BY Partition_Number 
       ORDER BY Partition_Number;

    The results look like this:

          Count(*)  Partition_Number
       -----------  ----------------
                 1                 1
                 1                 2

    Example  

    The following example creates a table partitioned with orders data for each quarter in 2008.

       CREATE TABLE Orders
         (O_orderkey INTEGER NOT NULL,
          O_custkey INTEGER,
          O_orderperiod PERIOD (DATE) NOT NULL,
          O_orderpriority CHAR (21),
          O_comment VARCHAR (79))
         PRIMARY INDEX (O_orderkey)
         PARTITION BY 
            CASE_N (END (O_orderperiod) <= date'2008-03-31', /* First Quarter */
                    END (O_orderperiod) <= date'2008-06-30', /* Second Quarter */
                    END (O_orderperiod) <= date'2008-09-30', /* Third Quarter */
                    END (O_orderperiod) <= date'2008-12-31' /* Fourth Quarter */
                   );

    The following SELECT statement scans two partitions and displays the details of the orders placed for the first two quarters.

    SELECT * 
    FROM Orders 
    WHERE END (O_orderperiod) > date'2008-06-30';

    Example  

    The following example uses IS [NOT] UNTIL_CHANGED in the PPI partitioning expression to check whether or not the ending bound of a Period expression is UNTIL_CHANGED.

       CREATE TABLE TESTUC
         (A  INTEGER,
          B  PERIOD (DATE),
          C INTEGER)
         PRIMARY INDEX (A)
         PARTITION BY
            CASE_N (END (b) IS UNTIL_CHANGED,
                    END (b) IS NOT UNTIL_CHANGED, UNKNOWN);

    Example  

    The following example uses IS [NOT] UNTIL_CLOSED in the PPI partitioning expression to check whether or not the ending bound of a transaction time column is UNTIL_CLOSED.

                    CREATE TABLE TESTUCL
                     (A  INTEGER,
                      B  PERIOD (TIMESTAMP (6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME,
                      C INTEGER)
                     PRIMARY INDEX (A)
                     PARTITION BY
                        CASE_N (END (b) IS UNTIL_CLOSED,
                                END (b) IS NOT UNTIL_CLOSED, UNKNOWN);

    Example  

    In this example, the session collation is ASCII.

    CASE_N (a<'b', a>='ba' and a<'dogg' and b<>'cow', c<>'boy', NO CASE OR UNKNOWN)

    The following table shows the result value returned by the above CASE_N function given the specified values for a, b, and c. x and y represent any value or NULL. The value 4 is returned when all the conditions are FALSE, or a condition is UNKNOWN with all preceding conditions evaluating to FALSE.

     

    a

    b

    c

    Result

    'a'

    x

    y

    1

    'boy'

    'girl'

    y

    2

    'boy'

    NULL

    y

    4

    'boy'

    'cow'

    'man'

    3

    'boy'

    'cow'

    'boy'

    4

    'dog'

    'ball'

    y

    2

    'dogg'

    x

    NULL

    4

    'dogg'

    x

    'man'

    3

    'egg'

    x

    'boy'

    4

    'egg'

    x

    NULL

    4

    'egg'

    x

    'girl'

    3

    Example  

    In this example, the session collation is ASCII.

    CASE_N (a<'b', a>='ba' and a<'dogg' and b<>'cow', c<>'boy', UNKNOWN)

    The following table shows the result value returned by the above CASE_N function given the specified values for a, b, and c. The x and y represent any value or NULL. The value 4 is returned if a condition is UNKNOWN with all preceding conditions evaluating to FALSE. NULL is returned if all the conditions are false.

     

    a

    b

    c

    Result

    'a'

    x

    y

    1

    'boy'

    'girl'

    y

    2

    'boy'

    NULL

    y

    4

    'boy'

    'cow'

    'man'

    3

    'boy'

    'cow'

    'boy'

    NULL

    'dog'

    'ball'

    y

    2

    'dogg'

    x

    NULL

    4

    'dogg'

    x

    'man'

    3

    'egg'

    NULL

    'boy'

    NULL

    'egg'

    x

    'boy'

    NULL

    'egg'

    x

    NULL

    4

    'egg'

    x

    'girl'

    3

    Example  

    In this example, the session collation is ASCII when submitting the CREATE TABLE statement, and the pad character is <space>. The example defines two partitions (numbered 1 and 2) based on the value of a:

  • The value of a is between 'a         ' (a followed by 9 spaces) and 'b         '.
  • The value of a is between 'b         ' and 'c         '.
  •    CREATE SET TABLE t2
          (a VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
           b INTEGER)
       PRIMARY INDEX (a)
       PARTITION BY CASE_N(a BETWEEN 'a' AND 'b', a BETWEEN 'b' AND 'c');

    The following INSERT statement inserts a character string consisting of a single <tab> character between the 'b' and '1'.

       INSERT t2 ('b	1', 1);

    The following INSERT statement inserts a character string consisting of a single <space> character between the 'b' and '1'.

       INSERT t2 ('b 1', 2);

    The following SELECT statement shows the result of the INSERT statements. Since the <tab> character has a lower code point than the <space> character, the first string inserted maps to partition 1.

       SELECT PARTITION, a, b FROM t2 ORDER BY 1;
     
       *** Query completed. 2 rows found. 3 columns returned.
       *** Total elapsed time was 1 second.
     
       PARTITION  a          b
     -----------  ------ -----
               1  b 1        1   (string contains single <tab> character)
               2  b 1        2   (string contains single <space> character)

    Related Topics

     

    For information on …

    See …

    PPI properties and performance considerations

    Database Design.

    PPI considerations and capacity planning

    the specification of a PPI for a table

    CREATE TABLE in SQL Data Definition Language.

    the specification of a PPI for a join index

    CREATE JOIN INDEX in SQL Data Definition Language.

    the modification of the partitioning of the primary index for a table

    ALTER TABLE in SQL Data Definition Language.

    the reconciliation of the partitioning based on newly resolved CURRENT_DATE and CURRENT_TIMESTAMP values

    ALTER TABLE TO CURRENT in SQL Data Definition Language