Archives of the TeradataForum

Message Posted: Wed, 05 Jan 2005 @ 11:33:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

We can only drop partitions if the partitioning expression is derived from the RANGE_N function. This cannot be done for the CASE_N function.

Instead you can use ALTER TABLE to modify the primary index and specify the entire partition expression excluding the partitions that need to be dropped, provided the table is empty.

     Example:
     --------
     show table t1;

      *** Text of DDL statement returned.

      *** Total elapsed time was 1 second.

     -----------------------------------------------
     CREATE SET TABLE TEST.t1 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           a INTEGER,
           b INTEGER)
     PRIMARY INDEX ( a )
     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ),
     (a >  200 ) AND  (a <=  300 ));


     ALTER TABLE t1 MODIFY PRIMARY INDEX

     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ));

     ALTER TABLE t1 MODIFY PRIMARY INDEX
     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ));

      *** Table has been modified.

      *** Total elapsed time was 1 second.


     show table t1;

      *** Text of DDL statement returned.

      *** Total elapsed time was 1 second.

     ------------------------------------------------
     CREATE SET TABLE TEST.t1 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           a INTEGER,
           b INTEGER)
     PRIMARY INDEX ( a )
     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ));

Thanks,

Vimala.

	<Prev	Next>		<<First	<Prev	Next>	Last>>