Example: Showing the Count of Rows in Each Partition - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
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