Query that flattens nested table(Oracle Example)

Lesson 3	How to flatten a nested table
Objective	Write a query that flattens a nested table

Write a Query that Flattens a Nested Table in Oracle

Flattening a nested table means querying the nested table within an outer table. This can be accomplished by using either the THE keyword or the TABLE keyword. We will use THE and TABLE throughout this module to familiarize you with using both the functions.
These two functions, which can be used within Data Manipulation Language (DML) statements such as INSERT, UPDATE, or DELETE, query the outer table as if it were a single table, even though it is a table within a table. For example, the SALE_HEADER table has a nested table.
In the following evaluative simulation, you will look at a query that flattens a nested table. You will use the SALE_HEADER (outer table) and DETAIL_TABLE (inner or nested table) tables, and will query from the nested table where sale_id is 101 for the outer table.

Flatten nested Table Query
In the next lesson, you will learn the easiest way to query a varray.

Querying Nested Tables

Click the link below to read about querying a nested table that has a reference operator (REF).
Querying Nested Tables

Nested Tables

Whereas varying arrays have a limited number of entries, a second type of collector, nested tables, has no limit on the number of entries per row. A nested table is, as its name implies, a table within a table. In this case, it is a table that is represented as a column within another table. You can have multiple rows in the nested table for each row in the main table.
For example, if you have a table of animal breeders, you may also have data about their animals. Using a nested table, you could store the information about breeders and all of their animals within the same table.
Consider the ANIMAL_TY datatype.

create or replace type ANIMAL_TY as object
(Breed VARCHAR2(25),
Name VARCHAR2(25),
BirthDate DATE);

Note: To keep this example simple, this version of the ANIMAL
The ANIMAL_TY datatype contains a record for each animal, its breed, name, and birthdate. To use this datatype as the basis for a nested table, you need to create a new abstract datatype:

create type ANIMALS_NT as table of ANIMAL_TY;

The as table of clause of this create type command tells Oracle that you will be using this type as the basis for a nested table. The name of the type, ANIMALS_NT, has a pluralized root to indicate that it stores multiple rows, and has the suffix NT to indicate that it will be a nested table.
You can now create a table of breeders, using the ANIMALS_NT datatype:

create table BREEDER
(BreederName VARCHAR2(25),
Animals ANIMALS_NT)
nested table ANIMALS store as ANIMALS_NT_TAB;

In this create table command, the BREEDER table is created. The first column of the BREEDER table is a Breeder Name column. The second column is a column named Animals, whose definition is the nested table ANIMALS_NT:

create table BREEDER
(Breeder Name VARCHAR2(25),
Animals ANIMALS_NT);

When creating a table that includes a nested table, you must specify the name of the table that will be used to store the nested table's data. That is, the data for the nested table is not stored inline with the rest of the table's data. Instead, it is stored apart from the main table. Thus, the data in the Animals column will be stored in one table, and the data in the Name column will be stored in a separate table. Oracle will maintain pointers between the tables. In this example, the out-of-line data for the nested table is stored in a table named ANIMALS_NT_TAB:

nested table ANIMALS store as ANIMALS_NT_TAB;

This example highlights the importance of naming standards for collectors. If you pluralize the column names ("Animals" instead of "Animal") and consistently use suffixes (TY, NT, and so forth), you can tell what an object is just by looking at its name. Furthermore, since nested tables and varying arrays may be based directly on previously defined datatypes, their names may mirror the names of the datatypes on which they are based. Thus, a datatype named ANIMAL_TY could be used as the basis for a varying array named ANIMALS_VA or a nested table named ANIMALS_NT. The nested table would have an associated out-of-line table named ANIMALS_NT_TAB. If you know that a table named ANIMALS_NT_TAB exists, you automatically know that it stores data based on a datatype named ANIMAL_TY.