Assume the following base table definition:
CREATE TABLE orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHARACTER(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_orderpriority CHARACTER(21), o_clerk CHARACTER(16), o_shippriority INTEGER, o_comment VARCHAR(79)) UNIQUE PRIMARY INDEX (o_orderkey, o_orderdate);
The following SQL text creates a single-level PPI join index on the base table named orders. Because the o_totalprice column has a data type of DECIMAL(13,2), o_totalprice values can have more digits than an INTEGER type can handle, so you might see errors when you insert values into o_totalprice because the definition of ordJI1 casts o_totalprice values as INTEGER values in its partitioning expression.
CREATE JOIN INDEX ordJI1 AS SELECT o_custkey, o_totalprice FROM orders PRIMARY INDEX (o_custkey) PARTITION BY RANGE_N(CAST(o_totalprice AS INTEGER) BETWEEN 0 AND 999999 EACH 100, NO RANGE);