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