Archives of the TeradataForum

Message Posted: Tue, 21 Dec 2010 @ 22:31:32 GMT


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

Subj:		Re: PPI Table - ALTER Assistance - help!!!

From:		John_Wight

Dieter Wrote earlier: You can't, because it's CASE_N not RANGE_N. Maybe this will work:

          > PARTITION BY RANGE_N(
          > joiso between 20090031 and 20091231 each 100,
          >                20100031 and 20101231 each 100,
          >                20110031 and 20111231 each 100,
          >   NO RANGE);

And now you might use ADD RANGE :-)

Actually the column 'joiso' is an integer but has the date in it = that's what the number represent. Our 'ETL Tool' suggested we do the following and it worked. Need to test though but just thought I'd extend an 'answer'. Yes we did use Range after CASTING the value to a valid date:

     PRIMARY INDEX nupi_aswift_dspfpclog_stg ( oxsite ,oxrepr )
     PARTITION BY RANGE_N(cast(cast(joiso AS CHAR(8)) AS DATE FORMAT 'yyyymmdd')
     BETWEEN '2008-12-31' AND '2015-12-31'  EACH INTERVAL '1' MONTH ,
      NO RANGE)
      INDEX nusi01_aswift_dspfpclog_stg ( joiso );

And we can extend the partitions with ALTER . . .

Thanks again Dieter for pointing the way!


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

Attachments

Library

Quick Reference