0

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
  • Please tag the question with your Oracle version – Jack Douglas Jul 15 '16 at 8:35
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.