Wednesday, April 26, 2017

How to access data from nested tables using SELECT queries?

Nested table is Oracle, How to access the data from nested table in Oracle, SELECT queries in Oracle to access nested table data, TABLE() function in Oracle, Unnesting the nested table data


How to access data from nested tables using SELECT queries?


Nested tables can be accessed using TABLE( ) function as follows; let us consider the table from the previous post here;  

CREATE TABLE Bills
(
Cust_ID CHAR(10),
Name VARCHAR(30),
Phone NUMBER(10),
Bill_Date DATE,
Billed_Items Items_Purchased_Tab
)
NESTED TABLE Billed_Items STORE AS Billed_Items_Tab;
Note: All words in ALL CAPS are keywords.

In the above table, Billed_Items is the column that is of type Nested table.
To display all the records from bills table, the query is as follows;

Query 1
SELECT * FROM Bills;

The result will be;

Cust_ID               Name                    Phone                   Bill_Date            
----------- ----------- ----------- ----------------
Billed_Items(Item_ID, Quantity, Price)
------------------------------------------------------
C101                      Raghu                   9878676500         04-JUN-2016
Items_Purchased_Tab(Items_Purchased_Typ(‘I101’, 2, 300.00), Items_Purchased_Typ(‘I106’, 2, 30.00), Items_Purchased_Typ(‘I110’, 1, 500.00))

To access individual attributes of nested table, we can write the query as follows;
Query 2
SELECT p.* FROM Bills b, TABLE(b.Billed_Items) p;

In this query, the function TABLE ( ) will unnest (flatten) the nested table column Billed_Items for every record. For example, in our table, we have one record with customer id C101 and that customer has billed three items. Now the result of TABLE (b.Billed_Items) will be as follows (the nested table is flattened);
Item_ID
Quantity
Price
I101
2
300.00
I106
2
30.00
I110
1
500.00
Here, the headings Item_ID, Quantity and Price are the attributes of the type Items_Purchased_Typ. Refer here.

Hence the result of the Query 2 is as follows;
Item_ID               Quantity                              Price
------------                ------------                --------------------
I101                       2                              300.00
I106                       2                              30.00
I110                       1                              500.00

*****************
 

Go to Collection Types page

Go to Object database systems page

Go to Nested Tables page











No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery