Archives of the TeradataForum

Message Posted: Mon, 20 Dec 2010 @ 19:08:49 GMT

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

Have tables that we need to extend Partitions on . most are OK with Date Ranges and not a problem.

We have some stage tables that use RANGE_N as follows:

     CREATE MULTISET TABLE xxx_STAGE.axxxxx_billog_stg ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           biodr VARCHAR(7) NOT NULL,
           biseq VARCHAR(1)  NOT NULL,
           bicnt DECIMAL(2,0) NOT NULL,
           joiso DECIMAL(8,0) NOT NULL,
           jotime DECIMAL(6,0) NOT NULL,
           joseqn DECIMAL(10,0) NOT NULL,
           jocode VARCHAR(1)  NOT NULL,
           joentt VARCHAR(2) NOT NULL,
           .
           .
           .
           )
     UNIQUE PRIMARY INDEX upi_axxxxx_billog_stg
             ( biodr ,biseq , bicnt ,joiso ,jotime ,joseqn )
     PARTITION BY CASE_N(
     joiso <  20090131 ,
     joiso <  20090231 ,
     joiso <  20090331 ,
     joiso <  20090431 ,
     joiso <  20090531 ,
     joiso <  20090631 ,
     joiso <  20090731 ,
     joiso <  20090831 ,
     joiso <  20090931 ,
     joiso <  20091031 ,
     joiso <  20091131 ,
     joiso <  20091231 ,
     joiso <  20100131 ,
     joiso <  20100231 ,
     joiso <  20100331 ,
     joiso <  20100431 ,
     joiso <  20100531 ,
     joiso <  20100631 ,
     joiso <  20100731 ,
     joiso <  20100831 ,
     joiso <  20100931 ,
     joiso <  20101031 ,
     joiso <  20101131 ,
     joiso <  20101231 ,
      NO CASE);

We now need to extend partitions for 2011 and 2012 as per 2009 and 2010 above.

Tried many variances of ALTER without luck - must be doing something wrong - ??

Anyone our there had to do this/know how and if so, what is syntax?

Thanks in advance;-)

e.g. Someting like following (this doesn't work - here for example of what we might expect, etc.);

     ALTER TABLE xxx_STAGE.upi_axxxxx_billog_stg
     MODIFY
      PRIMARY INDEX
     ADD RANGE
     (
     joiso <  20110131 ,
     joiso <  20110231 ,
     joiso <  20110331 ,
     joiso <  20110431 ,
     joiso <  20110531 ,
     .
     .
     .
     ;

JK

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