15.00 - IMMEDIATELY SUCCEEDS - 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)

IMMEDIATELY SUCCEEDS

Purpose  

Evaluates two Period expressions or derived periods.

Syntax  

where:

 

Syntax element …

Specifies …

period_expression

any expression that evaluates to a Period data type.

Note: The Period expression specified must be comparable with the other expression. Implicit casting to a Period data type is not supported.

derived_period

any expression that evaluates to a derived period.

Result Value

This predicate applies when both x and y are a period column, period constructor, or period value expression. In this case, the predicate returns True if the begin bound value of x is equal to the end bound value of y. If either or both of the operands are NULL, the result is UNKNOWN.

Example  

Assume that in employee1 table, created by the following CREATE TABLE statement, period1 and period2 are PERIOD (DATE) columns

CREATE TABLE employee1 (
       eid INTEGER NOT NULL,      
       name VARCHAR(100) NOT NULL,
       deptno INTEGER NOT NULL,
       period1(date),
       period2(date)
   ) PRIMARY INDEX(eid);
 

EID

Name

DeptNo

Period 1

Period 2

1

Adams

101

('2005-02-03', '2006-02-03')

('2005-02-03', '2006-02-03')

2

Mary

201

('2005-04-02', '2006-01-03')

('2006-01-03', '2007-02-03')

3

Jones

301

('2004-01-02', '2005-03-05')

('2003-03-05', '2004-01-02')

In the following SQL statement, IMMEDIATELY SUCCEEDS is used with period columns of the employee1 table:

SELECT eid, name, deptno, period1, period2 
FROM employee1 
WHERE period1 IMMEDIATELY SUCCEEDS period2;

The result is:

 

EID

Name

DeptNo

Period 1

Period 2

3

Jones

301

('2004-01-02', '2005-03-05')

('2003-03-05', '2004-01-02')

Example  

Assume that in the employee table, created by the following CREATE TABLE statement, jobdur1 and jobdur2 are derived period columns.

CREATE TABLE employee (
   eid INTEGER NOT NULL, 
   name VARCHAR(100) NOT NULL,
   deptno INTEGER NOT NULL,
   jobst1 DATE NOT NULL,
   jobend1   DATE NOT NULL,
   PERIOD FOR jobdur1(jobst1, jobend1),
   jobst2 DATE NOT NULL,
   jobend2   DATE NOT NULL,
   PERIOD FOR jobdur2(jobst2, jobend2)
   ) PRIMARY INDEX(eid);
 

EID

Name

DeptNo

JobSt1

JobEnd1

JobSt2

JobEnd2

1

Tom

101

DATE'2001-01-01'

DATE'2004-01-01'

DATE'2005-01-01'

DATE'2006-01-01'

2

Rick

201

DATE'2005-01-01'

DATE'2006-01-01'

DATE'2001-01-01'

DATE'2004-01-01'

3

Joo

301

DATE'2005-01-01'

DATE'2006-01-01'

DATE'2006-01-01'

DATE'2007-01-01'

4

Tam

401

DATE'2001-01-01'

DATE'2006-01-01'

DATE'2002-01-01'

DATE'2004-01-01'

5

Pat

501

DATE'2005-01-01'

DATE'2007-01-01'

DATE'2006-01-01'

DATE'2008-01-01'

6

Jack

601

DATE'2005-01-01'

DATE'2007-01-01'

DATE'2007-01-01'

DATE'2008-01-01'

7

Yu

701

DATE'2005-01-01'

DATE'2007-01-01'

DATE'2004-01-01'

DATE'2005-01-01'

8

Tim

801

DATE'2005-01-01'

DATE'2007-01-01'

DATE'2005-01-01'

DATE'2007-01-01'

In the following SQL statement, IMMEDIATELY SUCCEEDS is used with derived period columns of the employee table:

SELECT eid, name, depno, jobst1, jobend1, jobst2, jobend2 
FROM employee 
WHERE jobdur1 IMMEDIATELY SUCCEEDS jobdur2;

The result is:

 

EID

Name

DeptNo

JobSt1

JobEnd1

JobSt2

JobEnd2

7

Yu

701

DATE'2005-01-01'

DATE'2007-01-01'

DATE'2004-01-01'

DATE'2005-01-01'