16.20 - Example: Using CASE_N in a List of Partitioning Expressions that Define a Multilevel PPI - Advanced SQL Engine - Teradata Database

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.