15.00 - IS UNTIL_CLOSED/IS NOT UNTIL_CLOSED - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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