Example: Using CASE_N in a List of Partitioning Expressions that Define a Multilevel PPI

Teradata Vantage™ SQL Functions, Expressions, and Predicates

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

Here is an example that modifies “Example: Defining the Partition to Which a Row is Assigned” 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'.