Valid time temporal table insert statement failed

Database
Teradata Employee

Valid time temporal table insert statement failed

HI All,

i am trying to create a validtime temporal table by below DDL statemnent where deptduration is derived column and when i inserting below mentioned record , its failed saying can not place null value in not null column.

 

Note : As per the teradata temporal docs( TD 15.00)the record is inserted where valid time column having values

 ('10/10/05', '99/12/31')  where 10/10/05 is temporal date 

 

DDL :

 

CREATE MULTISET TABLE department_vt1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( deptname VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, deptno INTEGER NOT NULL, deptduration PERIOD(DATE) NOT NULL AS VALIDTIME) PRIMARY INDEX ( deptname ); 2b.)

Insert the following row.

 

DML :

INSERT department_vt1('Acct',805); 

 

can anyone help me on this why its not working for me . does it require any setting ?

 

Br,

Arun  parmar 

 

7 REPLIES
Enthusiast

Re: Valid time temporal table insert statement failed

I am new to teradata,  just tried with below statement.

 

INSERT into department_vt1
(deptname,deptno,deptduration) values
('Acct',805,period(date '2016-12-13',date '9999-12-31'));

Teradata Employee

Re: Valid time temporal table insert statement failed

i checked  the mentioned insert  statemnent already and it was working fine but its not my concern. 

i want to know the reason why its not working as per the teradata docs.

is that any setting need to be change or it wont work in TD 15.00 version ?

 

Teradata Employee

Re: Valid time temporal table insert statement failed

The NOT NULL attribute requires you to specify a value. If you omit NOT NULL or specify NOT NULL WITH DEFAULT (or even specify NULL), then the system default values will be supplied as you expected.

Enthusiast

Temporal table insert statement failed :VALIDTIME and TRANSACTIONTIME

I have created temporal table as below:

CREATE MULTISET TABLE XYZ. CUSTOMER_PROD_DETAILS
(
CUST_NO INTEGER
,PRODUCT_NO INTEGER
,PRODUCT_VAIIDL_TIME PERIOD(DATE) NOT NULL AS VALIDTIME
,PRODUCT_TRANSACTION_TIME PERIOD (TIMESTAMP(6) with TIME ZONE) NOT NULL AS TRANSACTIONTIME
)
PRIMARY INDEX(PRODUCT_NO);

 

Then I tried to insert data as below :

INSERT INTO XYZ. CUSTOMER_PROD_DETAILS
(cust_no,PRODUCT_NO,PRODUCT_VAIIDL_TIME,PRODUCT_TRANSACTION_TIME)
values(10,101, DATE '2011-01-01' ,DATE '9999-12-31', TIMESTAMP(6) '2011-01-01 08:09:290000' ,TIMESTAMP(6) '9999-12-31 23:59:59.999999' );

 

But when I select, the PRODUCT_TRANSACTION_TIME is  :(2018-01-11 10:07:14.940000-05:00, 9999-12-31 23:59:59.999999+00:00)

 

The TRANSACTION_TIME date has changed to the current_date automatically. Any reason for this ? Then how will the time period be captured for old records ?

Pls help.

 

Teradata Employee

Re: Temporal table insert statement failed :VALIDTIME and TRANSACTIONTIME

Transaction Time is always system time for INSERT, UPDATE, etc. by design.

 

For exceptional circumstances, Teradata has a special restricted NONTEMPORAL privilege. A query with the NONTEMPORAL modifier treats ValidTime and TransactionTime as ordinary date/timestamp columns.

Enthusiast

Re: Temporal table insert statement failed :VALIDTIME and TRANSACTIONTIME

Thanks Fred!

The Transaction time always has to be NOT NULL ,So when this column is defined as NOT NULL ,and we are not providing values for it, it gives error.

 

We can not mention :with default" as well, it throws error.

Then we always have to mention current timestamp here. Is this the only solution ? How can i define the table so that it takes the system dates while insert, without mentioning it.

If I get one example, it will be helpful.

 

Junior Contributor

Re: Temporal table insert statement failed :VALIDTIME and TRANSACTIONTIME

You don't need to specifiy valid time, you simply prepend the insert with CURRENT VALIDTIME and omit the VT/TT columns:

 

CURRENT VALIDTIME 
INSERT INTO CUSTOMER_PROD_DETAILS
(cust_no,PRODUCT_NO)
VALUES(10,101);
or
CURRENT VALIDTIME 
INSERT INTO CUSTOMER_PROD_DETAILS
VALUES(10,101);

The following will be used when you want to overwrite the default validity

INSERT INTO CUSTOMER_PROD_DETAILS
(cust_no,PRODUCT_NO,PRODUCT_VAIIDL_TIME)
VALUES(10,101, PERIOD(DATE '2011-01-01' ,DATE '9999-12-31'));

See the  manuals:

Example: Current Valid-Time Insert into a Valid-Time Table