Example : Using the .ALL Operator With Structured UDTs
Following UDT and table definitions for the *.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.
Use *.ALL
to retrieve all columns of the table student_record where the structured type columns high_school and college are expanded:
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';