15.00 - CONTAINS - 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)

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

  • If both expressions have a Period data type or a derived period, the function returns TRUE if the beginning bound of the first expression is less than or equal to the beginning bound of the second expression and the ending bound of the first expression is greater than or equal to the ending bound of the second expression; otherwise, the function returns FALSE.
  • If the first expression is a Period expression or a derived period and the second expression is a DateTime expression, the function returns TRUE if the beginning bound of the Period expression or derived period is less than or equal to the DateTime expression and the ending bound of the Period expression or derived period is greater than the DateTime expression; otherwise, the function returns FALSE.
  • If the first expression is a DateTime expression and the second expression is a Period expression, the function returns TRUE if the DateTime expression is less than or equal to beginning bound of the Period expression and the DateTime expression plus one granule is greater than or equal to the ending bound of the Period expression; otherwise, the function returns FALSE.
  • If either expression is NULL, the operator returns UNKNOWN.
  • 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'