IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED
Purpose
Tests the ending bound value of a temporal table system-time or transaction-time column to see whether the row is open (the ending bound value IS UNTIL_CLOSED) or closed (the ending bound value IS NOT UNTIL_CLOSED).
For more information about temporal tables, see ANSI Temporal Table Support and Temporal Table Support.
Syntax
where:
|
Syntax element … |
Specifies … |
|
period_expression |
a reference to a transaction-time column. |
|
derived_period |
a reference to a system-time or transaction-time column defined using a derived period. |
Usage Notes
When a row is created in a temporal table that has a system-time or transaction-time dimension (column), Teradata Database sets the ending bound of the column to UNTIL_CLOSED and the row is considered open. When the row is closed, Teradata Database sets the ending bound value to the closing timestamp.
IS UNTIL_CLOSED evaluates to true if the ending bound of the specified column is the maximum timestamp value, 9999-12-31 23:59:59.999999+00:00.
Example
CREATE MULTISET TABLE employee(
empno INTEGER,
ename VARCHAR(50),
deptno INTEGER,
jobstart TIMESTAMP WITH TIME ZONE NOT NULL
GENERATED ALWAYS AS ROW START,
jobend TIMESTAMP WITH TIME ZONE NOT NULL AS
GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(jobstart,jobend)
)
PRIMARY INDEX(empno) WITH SYSTEM VERSIONING;
Assume the table contains the following row:
empno ename deptno jobstart jobend
----- ------ ------ -------------------------------- --------------------------------
1025 John 999 2005-02-03 12:12:12.123456+00:00 9999-12-31 23:59:59.999999+00:00
The following SELECT statements would give these results:
SELECT empno,ename(CHAR(6)) FROM employee
WHERE END(SYSTEM_TIME) IS UNTIL_CLOSED;
empno ename
------ ------
1025 John
SELECT empno,ename(CHAR(6)) FROM employee
WHERE END(SYSTEM_TIME) IS NOT UNTIL_CLOSED;
*** Query completed. No rows found.
Example
If we assume that the employee table, created by the following SQL statement:
CREATE MULTISET TABLE employee(
id INTEGER,
name VARCHAR(50),
dept INTEGER,
tt PERIOD(TIMESTAMP(6)WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME
)
PRIMARY INDEX(id);
contains the following row, a real period:
INSERT INTO employee(102, 'John', 222);
The following SELECT statement:
SELECT * FROM employee WHERE END(tt) IS UNTIL_CLOSED;
returns:
id name dept
--------------------
102 John 222