17.05 - Example: EXPAND ON With a Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)

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.

id name duration_begin_end
101 ABC 2004-01-01, 2004-12-31

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.

name student_id course_begin_end
CPP 101 2004-08-01,2004-08-30
Java 101 2004-07-01,2004-07-30
C 101 2004-04-01,2004-06-30

Submit a SELECT statement that returns the month a student was enrolled in a particular 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.

course-name extract(month from expd)
C 4
C 5
C 6
Java 7

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