Example: EXPAND ON with a Join - Teradata Vantage

This example shows using a join with the EXPAND ON clause.

First create the student table.

     CREATE SET TABLE student (
       id                 INTEGER,
       name               CHARACTER(10) CHARACTER SET LATIN
                          NOT CASESPECIFIC,
       duration_begin_end PERIOD(DATE))
     PRIMARY INDEX (id);

The student table contains the following row.

Now create the course table.

     CREATE SET TABLE course (
       name             CHARACTER(10) CHARACTER SET LATIN
                        NOT CASESPECIFIC,
       student_id       INTEGER,
       course_begin_end PERIOD(DATE))
     PRIMARY INDEX (name);

The course table contains the following rows.

Submit a SELECT statement that returns the month a student was enrolled in a specified course.

This statement joins the student table (expanded on an interval literal and aliased as dt) with the course table using a mix of equality and inequality predicates in its WHERE clause.

     SELECT course.name, EXTRACT(MONTH FROM BEGIN(expd)
     FROM (SELECT student_id, expd
           FROM student
           EXPAND ON duration_begin_end AS expd BY INTERVAL '1' MONTH)
                     AS dt, course AS c
     WHERE c.student_id = dt.id
     AND   (BEGIN(c.course_begin_end) < END(expd)
     AND    BEGIN(expd) < END(c.course_begin_end)
     AND    dt.id = 101;

This statement returns the following five rows.

The database also returns a 9308 warning message for this statement.