Error: Invalid operation for DateTime or Interval

Database
Fan

Error: Invalid operation for DateTime or Interval

Pretty new to Teradata and I've been stuck on this error for a little bit.

Trying to fill an empty table with data from a differenet table. Everything is the exact same between the tables, except for the partition. 

INSERT INTO Measurement_Fact_1

SELECT * FROM Measurement_Fact;

I'm assuming it has to do with the Load_Ts column, which is TIMESTAMP(0)

5 REPLIES
Enthusiast

Re: Error: Invalid operation for DateTime or Interval

Sean,

Please supply the complete DDL fro both tables and someone can help.

Rglass

Fan

Re: Error: Invalid operation for DateTime or Interval

I forgot that the primary index was also changed. 

CREATE SET TABLE Measurement_Fact ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Sensor_Id INTEGER NOT NULL,

      Timestamp_ID INTEGER NOT NULL,

      Geo_Point_ID INTEGER NOT NULL,

      Well_ID INTEGER NOT NULL,

      Measure_Type_ID INTEGER NOT NULL,

      Source_ID INTEGER NOT NULL,

      Measure_Value DECIMAL(38,6),

      Load_Ts TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0),

      Measure_Date DATE FORMAT 'yyyy-mm-dd')

UNIQUE PRIMARY INDEX (Sensor_Id ,Timestamp_ID ,Geo_Point_ID ,

Well_ID ,Measure_Type_ID ,Source_ID )

PARTITION BY ( RANGE_N(Well_ID  BETWEEN 1  AND 20  EACH 1 ,

 NO RANGE OR UNKNOWN),RANGE_N(Measure_Type_ID  BETWEEN 1  AND 1000  EACH 1 ,

 NO RANGE OR UNKNOWN) );

CREATE SET TABLE Measurement_Fact_1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Sensor_Id INTEGER NOT NULL,

      Timestamp_ID INTEGER NOT NULL,

      Measure_Date DATE FORMAT 'yyyy-mm-dd',

      Geo_Point_ID INTEGER NOT NULL,

      Well_ID INTEGER NOT NULL,

      Measure_Type_ID INTEGER NOT NULL,

      Source_ID INTEGER NOT NULL,

      Measure_Value DECIMAL(38,6),

      Load_Ts TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0))

PRIMARY INDEX ( Timestamp_ID ,Well_ID ,Measure_Type_ID )

PARTITION BY RANGE_N(Measure_Date  BETWEEN DATE '2006-01-01' AND '2015-12-31' EACH INTERVAL '1' DAY, NO RANGE, UNKNOWN);

Junior Contributor

Re: Error: Invalid operation for DateTime or Interval

Everything is the exact same between the tables

The order of columns is different and you're doing a "SELECT *".

Simply switch to the correct column list:

INSERT INTO Measurement_Fact_1
SELECT
Sensor_Id, Timestamp_ID, Measure_Date, Geo_Point_ID, Well_ID,
Measure_Type_ID, Source_ID, Measure_Value, Load_Ts
FROM Measurement_Fact;

Btw, you should extend the RANGE_N far into the future to avoid any need to modify it again as empty partitions have zero overhead, e.g.

PARTITION BY RANGE_N(Measure_Date  BETWEEN DATE '2006-01-01' AND '2030-12-31' EACH INTERVAL '1' DAY, NO RANGE, UNKNOWN);

Similar instead of DROPing old partitions simply DELETE the data in it.

Fan

Re: Error: Invalid operation for DateTime or Interval

Dieter,

Thank you! Not sure how I couldn't figure it out, but sometimes you just need a second set of eyes on it. Good point on extending the range. 

Sean 

Re: Error: Invalid operation for DateTime or Interval

dNoeth, I've been using Teradata for about 8 months and have been reading your answers to questions since probably Day 1.  Much of what I read is years old but your answers are still relevant to my problems.  Thanks for taking the time to write clear, concise answers.  I also appreciate the fact that you're not rude to people just getting started who sometimes make what to an experienced person are silly mistakes.