IMMEDIATELY PRECEDES
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 end bound value of x is equal to the begin 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 PRECEDES is used with period columns of the employee1 table:
SELECT eid, name, depno, period1, period2
FROM employee1
WHERE period1 IMMEDIATELY PRECEDES period2;
The result is:
|
EID |
Name |
DeptNo |
Period 1 |
Period 2 |
|
2 |
Mary |
201 |
('2005-04-02', '2006-01-03') |
('2006-01-03', '2007-02-03') |
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 PRECEDES is used with derived period columns of the employee table:
SELECT eid, name, deptno, jobst1, jobend1, jobst2, jobend2
FROM employee
WHERE jobdur1 IMMEDIATELY PRECEDES jobdur2;
The result is:
|
EID |
Name |
DeptNo |
JobSt1 |
JobEnd1 |
JobSt2 |
JobEnd2 |
|
3 |
Joo |
301 |
DATE'2005-01-01' |
DATE'2006-01-01' |
DATE'2006-01-01' |
DATE'2007-01-01' |
|
6 |
Jack |
601 |
DATE'2005-01-01' |
DATE'2007-01-01' |
DATE'2007-01-01' |
DATE'2008-01-01' |