Dynamic partition not working

Database
Enthusiast

Dynamic partition not working

Hi,

I am trying to insert data into table. Both source and target have same DDL structure. Source table has Dynamic partitions. Source DDL:

CREATE MULTISET TABLE db1.tb1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      CUSTID CHAR(3),

      SEGMENTG CHAR(9),

      VPERIOD DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      LOADED_TS TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0))

PRIMARY INDEX PI_CUSTICPR ( CUSTID )

PARTITION BY RANGE_N(VPERIOD  BETWEEN ADD_MONTHS(((DATE + 1 )- (EXTRACT(DAY FROM (DATE )))),(-36 )) AND DATE - (EXTRACT(DAY FROM (DATE ))) EACH INTERVAL '1' MONTH );

target DDL is exactly same. However i get partition voilation error when I do simple INSERT INTO db1.target SEL * from db1.tb1;

Can anyone help me if there is something wrong with dynamic partition.

4 REPLIES
Enthusiast

Re: Dynamic partition not working

Were both the tables created on the same date?

Enthusiast

Re: Dynamic partition not working

No, both tables are not on same date.

Enthusiast

Re: Dynamic partition not working

add a no range and unknown partition to partitioning phrase and retry your insert.  

Teradata Employee

Re: Dynamic partition not working

Sending a bunch of rows to NO RANGE partition would eliminate the error, but is unlikely to be the desired result.

Note that the definition of partitions based on CURRENT_DATE (what you are calling "dynamic partitioning") is established at CREATE time and is in fact static except at the points where you explicitly change via ALTER TABLE TO CURRENT.