Partitions on Character fields in 14.10

Database

Partitions on Character fields in 14.10

Forgive me if this is something realy basic. 

I want to create a partition (or a secondary partition) on a character column but want to define a range (like interval or each 1).

The field will store a date in 'YYYY-MM' format. with a '-' in the data. what is the best way to do it ?  

CREATE  MULTISET TABLE TABLE_1
(
COL_1 VARCHAR(16) ,
Y_MTH CHAR(7) ,
COL2 DECIMAL(18,0)
)
PRIMARY INDEX (APPN_ID)
PARTITION BY CASE_N (
Y_MTH BETWEEN '2009-01-' AND '2009-12',
Y_MTH BETWEEN '2010-01' AND '2010-12',
Y_MTH BETWEEN '2011-01' AND '2011-12',
Y_MTH BETWEEN '2012-01' AND '2012-12',
Y_MTH BETWEEN '2013-01' AND '2013-12',
Y_MTH BETWEEN '2014-01' AND '2014-12',
Y_MTH BETWEEN '2015-01' AND '2015-12',
Y_MTH BETWEEN '2016-01' AND '2016-12',
Y_MTH BETWEEN '2017-01' AND '2017-12',
Y_MTH BETWEEN '2018-01' AND '2018-12',
Y_MTH BETWEEN '2019-01' AND '2019-12',
Y_MTH BETWEEN '2020-01' AND '2020-12',
NO CASE, UNKNOWN);
2 REPLIES
Enthusiast

Re: Partitions on Character fields in 14.10

Hi, I tried with the below CT with CASE_N with LIKE condition in the partition. Im not sure about the performance. I tested inserting few rows, it was success. Not sure about larger data sets.

CREATE MULTISET TABLE DBNAME.TEST1

(

COL2 DECIMAL(18,0),

COL_1 VARCHAR(16) ,

Y_MTH CHAR(7)

)


PRIMARY INDEX (COL2)

PARTITION BY CASE_N (

Y_MTH LIKE '2009-%',

Y_MTH LIKE '2010%',

Y_MTH LIKE '2011-%',

Y_MTH LIKE '2012-%',

Y_MTH LIKE '2013-%',

Y_MTH LIKE '2014-%',

Y_MTH LIKE '2015-%',

Y_MTH LIKE '2016-%',

Y_MTH LIKE '2017-%',

Y_MTH LIKE '2018-%',

Y_MTH LIKE '2019-%',

Y_MTH LIKE '2020-%',

NO CASE, UNKNOWN);

Here is the output:








COL2 COL_1 Y_MTH PARTITION# PARTITION_TYPE
3 ravi ? 14 UNKNOWN
1 dinesh 2014-02 6 ACTUAL PARTITION
2 kumar 2014-02 6 ACTUAL PARTITION
3 ravi 2058-02 13 NO CASE
3 ravi        " " 13 NO CASE
3 ravi 123 13 NO CASE

Thanks,

Dinesh

Teradata Employee

Re: Partitions on Character fields in 14.10

If what is requested is to be able to specify an interval on the data ranges specified above, eg to get single month partitions, then that is not possible. There is no way to define math on character strings. Is it required to store these as character strings? If they could be stored as dates or integers then it would be possible to define the intervals. If it is necessary to keep the column as character, then each month would need to be enumerated in a case.