Example: Showing the Count of Rows in Each Partition - Analytics Database - Teradata Vantage

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