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
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.
can anyone help me on this why its not working for me . does it require any setting ?
I am new to teradata, just tried with below statement.
INSERT into department_vt1
('Acct',805,period(date '2016-12-13',date '9999-12-31'));
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 ?
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.
I have created temporal table as below:
CREATE MULTISET TABLE XYZ. CUSTOMER_PROD_DETAILS
,PRODUCT_VAIIDL_TIME PERIOD(DATE) NOT NULL AS VALIDTIME
,PRODUCT_TRANSACTION_TIME PERIOD (TIMESTAMP(6) with TIME ZONE) NOT NULL AS TRANSACTIONTIME
Then I tried to insert data as below :
INSERT INTO XYZ. CUSTOMER_PROD_DETAILS
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 ?
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.
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.
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:
I have encountered another issue for same table.Please check
1>done 1 insert :
INSERT INTO CUSTOMER_PROD_DETAILS1
2>done 1 update:
UPDATE CUSTOMER_PROD_DETAILS1 SET CUST_NO=30 WHERE PRODUCT_NO=201 ;
3>Then tried using NONSEQUENCED VALIDTIME SELECT * FROM CUSTOMER_PROD_DETAILS1 , but gives error "Mixing of ANSI temporal and Teradata temporal syntax is not allowed"
How to resolve this ?
4>So tried below sql to view all rows ,as suggested in Teradata 15 video by Tera tom:
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
SELECT * FROM CUSTOMER_PROD_DETAILS1 ;
But is shows 2 records..but as per the video there should be 3 records: for the old record(cust_no=2) ,1 new row was supposed to there which says the record was valid till which date. Why this is happening ?
|30||201||(2018-01-18, 9999-12-31)||(2018-01-18 06:30:34.950000-05:00, 9999-12-31 23:59:59.999999+00:00)|
|20||201||(2018-01-18, 9999-12-31)||(2018-01-18 06:30:25.510000-05:00, 2018-01-18 06:30:34.950000-05:00)|
There is a new rule came in ANSI Temporal feature (TD15.10).If you are specifying temporal qualifier for queries you must specify for all dimensions. In your failure case, your table is a bitemporal table and qualifier mentioned on your query is only valid time so for other dimension teradata inherits session qualifier default session qualifier is ANSI qualifier, Now your query will have two qualifiers one is old Teradata (TD.1310) and another dimension has ANSI qualifier which is not allowed. So you have two options here either you have to change session qualifier or specify qualifier in all dimensions.
Please let me know if you need help on syntaxes.