CONTAINS
Purpose
Evaluates two Period expressions, or derived periods, or DateTime expressions to TRUE, FALSE, or UNKNOWN.
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. |
|
datetime_expression |
any expression that evaluates to a DATE, TIME, or TIMESTAMP data type. |
|
derived_period |
any expression that evaluates to a derived period. |
Error Conditions
If either expression evaluates to a data type that is other than a Period or DateTime, an error is reported.
If the expressions do not have comparable data types, an error is reported.
Result Value
Example
Assume the following query is executed on the employee table where period1 and period2 are PERIOD(DATE) columns:
SELECT * FROM employee WHERE period2 CONTAINS period1;
|
ename |
period1 |
period2 |
|
Adams |
('2005-02-03', '2006-02-03') |
('2005-02-03', '2006-02-03') |
|
Mary |
('2005-04-02', '2006-01-03') |
('2005-02-03', '2006-02-03') |
|
Jones |
('2004-01-02', '2004-03-05') |
('2004-03-05', '2004-10-07') |
|
Randy |
('2004-01-02', '2004-03-05') |
('2004-03-07', '2004-10-07') |
|
Simon |
? |
('2005-02-03', '2005-07-27') |
The result is:
|
ename |
period1 |
period2 |
|
Adams |
('2005-02-03', '2006-02-03') |
('2005-02-03', '2006-02-03') |
|
Mary |
('2005-04-02', '2006-01-03') |
('2005-02-03', '2006-02-03') |
Example
Assume that in 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, CONTAINS is used with derived period columns of the employee table:
SELECT eid, name, jobst1, jobend1, jobst2, jobend2
FROM employee
WHERE jobdur1 CONTAINS jobdur2;
The result is:
|
EID |
Name |
DeptNo |
JobSt1 |
JobEnd1 |
JobSt2 |
JobEnd2 |
|
4 |
Tam |
401 |
DATE'2001-01-01' |
DATE'2006-01-01' |
DATE'2002-01-01' |
DATE'2004-01-01' |
|
8 |
Tim |
801 |
DATE'2005-01-01' |
DATE'2007-01-01' |
DATE'2005-01-01' |
DATE'2007-01-01' |