Cant access both phone numbers in the nested table type

oracleoracle-sql-developer

Im using Oracle Developer and I have a customers table where each customer has 1 home-phone number beginning with 0131 and either 1 or 2 mobile numbers. I am trying to display the customers name and phone number which contains 0770 and has more than 1 mobile phone number

I have a nested table in my database which contains the 2 sets of phone numbers. 

CREATE TYPE PHONENUMBER_TYPE AS OBJECT(
    int_code VARCHAR2(4),
    area     VARCHAR2(5),
    local    VARCHAR2(8));

CREATE TYPE PHONENUMBER_TABLE_TYPE AS TABLE OF PHONENUMBER_TYPE;

Here is my customers table and type

CREATE type customer_Type AS object(
    cust_ID NUMBER(8),
    address address_type,
    name name_type,
    homephone PHONENUMBER_TYPE,
    cust_mobiles PHONENUMBER_TABLE_TYPE,
    ninum VARCHAR2(8));

CREATE TABLE customer OF customer_Type(
     cust_ID PRIMARY KEY)
NESTED TABLE cust_mobiles STORE AS customer_mobiles;

And I'm entering data into the table using the following insert statement

  INSERT INTO customer VALUES(
      '1002',
      address_type('Adam', 'Edinburgh', 'EH1 6EA'),
      name_type('Mr', 'Jack', 'Smith'),
      phonenumber_type(NULL, '0131', '4960403'),
      phonenumber_table_type(PHONENUMBER_TYPE('44', '07889', '900003'), 
      PHONENUMBER_TYPE('44', '07705', '623443')),
      'NI810');

And obviously I am entering both phone numbers into phonenumber_table_type but I cant seem to access both when I'm using the where clause in my query.

SELECT t.name.firstname   AS fname,
       e.*
FROM customer t,
TABLE(t.cust_mobiles) e
WHERE e.area LIKE '0770%';

Which produces the following output:

+-------+-------+----------+-------+--------+
| FNAME | SNAME | INT_CODE | AREA  | LOCAL  |
+-------+-------+----------+-------+--------+
| Jack  | Smith |    44    | 07705 | 623443 |
+-------+-------+----------+-------+--------+

But the expected out put should be

+-------+-------+----------+-------+--------+----------+-------+--------+
| FNAME | SNAME | INT_CODE | AREA  | LOCAL  | INT_CODE | AREA  | LOCAL  |
+-------+-------+----------+-------+--------+----------+-------+--------+
| Jack  | Smith |    44    | 07705 | 623443 |    44    | 07889 | 900003 |
+-------+-------+----------+-------+--------+----------+-------+--------+

So how would I produce the expected result?

I though just adding in the another e.* again but that would produce the same number twice. 

SELECT t.name.firstname   AS fname,
       e.*, e.*
FROM customer t,
TABLE(t.cust_mobiles) e
WHERE e.area LIKE '0770%';

I suspect its got something to do with the where clause. Because when I run the following I get all the numbers in the phonenumber_table_type. 

SELECT C.name.firstname, U.* FROM customer c, TABLE(c.CUST_MOBILES) u

Best Answer

Please take a look at http://www.orafaq.com/wiki/NESTED_TABLE.

When you include the nested table it is like a join, so if you want a single row per customer, you will need to do some sort of unpivot (note that there could be more than two phone numbers in the nested table — Oracle needs to know how many columns to return. This will return all the rows:

SELECT t.name.firstname   AS fname,
       e.*
FROM customer t,
TABLE(t.cust_mobiles) e;

Useful hints for unpivoting can be found here: https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

Related Question