Example: EXPAND ON With a Join - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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.

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
CPP 8
Java 7

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