17.05 - Example: Current Modifications Do Not Apply to Future Rows - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

This example demonstrates that current data modifications do not apply to future rows. Assume the following tables describe a company’s employees and departments:

CREATE MULTISET TABLE employee ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      eid INTEGER NOT NULL,
      ename VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      bdate DATE FORMAT 'yyyy/mm/dd',
      job_duration PERIOD(DATE) NOT NULL AS VALIDTIME,
      deptid INTEGER,
      mid INTEGER)

PRIMARY INDEX ( eid );

CREATE MULTISET TABLE dept ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      deptid INTEGER NOT NULL,
      deptname VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( deptid );

Assume the company management must cut down the staff of the SUPPORT department. Employees who have been with the company for less than three months must be discharged. The following query removes employees with job durations of less than three months:

CURRENT VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
      dept.deptid = employee.deptid AND
      BEGIN(job_duration) > CURRENT_DATE - interval '3' month;

Now assume that the system includes employee entries for new employees who have not yet started working at the company. The DELETE statement above will not remove these future employees. To remove them together with the current employees, the following statement could be used:

BT;
/* delete currently employees in department with less than 3 months
work*/
CURRENT VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
      dept.deptid = employee.deptid AND
      BEGIN(job_duration) > current_date - interval '3' month;

/* delete all future employees */
SEQUENCED VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
      dept.deptid = employee.deptid AND
      BEGIN(job_duration) > TEMPORAL_DATE;

ET;

Alternatively, the following SQL would accomplish the same:

REPLACE VIEW v1 AS
NONSEQUENCED VALIDTIME
SELECT employee.eid, dept.deptid
FROM employee, dept
WHERE dept.deptname = 'SUPPORT' AND
 dept.deptid = employee.deptid AND
BEGIN(job_duration) > CURRENT_DATE - interval '3' month
AND job_duration OVERLAPS PERIOD(TEMPORAL_DATE, UNTIL_CHANGED);
SEQUENCED VALIDTIME PERIOD(TEMPORAL_DATE, UNTIL_CHANGED)
DELETE employee FROM v1 WHERE v1.eid = employee.eid AND 
 v1.deptid = employee.deptid;