.ALL Operator Examples: Using the .ALL Operator with Structured UDTs - Analytics Database - Teradata Vantage

The following UDT and table definitions are for the following *.ALL operator examples.

     CREATE TYPE school_record AS (
       school_name VARCHAR(20),
       GPA         FLOAT)
     INSTANTIABLE
     …
     CREATE TYPE college_record AS (
       school school_record,
       major  VARCHAR(20),
       minor  VARCHAR(20))
     INSTANTIABLE
     …
     CREATE TABLE student_record (
       student_id  INTEGER,
       Last_name   VARCHAR(20),
       First_name  VARCHAR(20),
       high_school school_record,
       college     college_record);

Example 1a: Using *.ALL to Retrieve All Table Columns, Expanding the Structured Types

This example shows a pair of SELECT statements that return equivalent response sets. The only difference is that one statement uses the *.ALL notation and the other does not.

     SELECT *.ALL
     FROM student_record;

The following equivalent SELECT statement does not use the *.ALL notation:

     SELECT student_id, last_name, first_name,
            high_school.school_name(), high_school.GPA(),   
            college.school().school_name(), college.school().GPA(),
            college.major(), college.minor()
     FROM student_record;

Example 1b: Using *.ALL to Retrieve the Expanded Structured Type for a Single UDT Column

Use *.ALL to retrieve all columns of the table student_record where the structured type columns high_school and college are expanded:

     SELECT student_record.*.ALL;

The following equivalent SELECT statement does not use the *.ALL notation:

     SELECT student_record.student_id, student_record.last_name,
            student_record.first_name,
            student_record.high_school.school_name(),
            student_record.high_school.GPA(),
            student_record.college.school().school_name(),
            student_record.college.school().GPA(),
            student_record.college.major(),
            student_record.college.minor();

Example 1c: Using *.ALL to Retrieve the Expanded Structure Type for Multiple UDT Columns

Retrieve high school names and grade point averages of all students:

     SELECT high_school.ALL
     FROM student_record;

The following equivalent SELECT statement does not use the .ALL notation:

     SELECT high_school.school_name(), high_school.GPA()
     FROM student_record;

Example 1d: Selecting the Information for Only One Student from a UDT

Retrieve the college name, GPA, major, and minor of student 'Steven Smith':

     SELECT s.college.ALL
     FROM student_record s
     WHERE s.student.First_name() = 'Steven'
     AND   s.student.Last_name() = 'Smith';

The following equivalent SELECT statement does not use the .ALL notation:

     SELECT s.college.school().school_name(), s.college.school().GPA(),
            s.college.major(), s.college.minor()
     FROM student_record s
     WHERE s.student.first_name() = 'Steven'
     AND   s.student.last_name() = 'Smith';