Nested Tables (Cont.)
Qualifiers are required for object attribute references.
Question: Display all their social security numbers of dependents of employee 101.
SQL> SELECT ssno FROM TABLE( employees.dependents )
2 WHERE employees.eid = 101;
SELECT ssno FROM TABLE(employees.dependents)
*
ERROR at line 1:
ORA-00904: "EMPLOYEES"."DEPENDENTS": invalid identifier
SQL> SELECT ssno FROM
2 TABLE( SELECT e.dependents FROM employees e
3 WHERE e.eid = 101 ) d;
SSNO
----------
322456776
123822332
|
|
There are two ways to retrieve from a table of tables:
- unnesting via table products and
- nested cursors.
Unnesting via Table Products
Q: Retrieve ssno column values from all the dependents of all employees.
- Each row in the first table is matched with each row of its own collection column value (converted to a table).
- To see a row for an employee with no dependents, with a null
ssno, add a ‘+’ to the dependents side.
SQL> SELECT e.eid, d.ssno
2 FROM employees e, TABLE( e.dependents )(+) d;
EID SSNO
---------- ----------
101 322456776
101 123822332
102 565534555
|
|
|
Beauty is only skin deep.
|