Re: REPOST: PLease help with querying nested tables.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 09 Sep 1999 08:41:44 -0400
Message-ID: <76rXN6VeHntCDQ5D35mMoUwFHqdt@4ax.com>

A copy of this was sent to juturi_at_workmail.com (if that email address didn't require changing) On Thu, 09 Sep 1999 05:41:50 GMT, you wrote:

>
>
>
>
> (Oracle8i, Red Hat Linux 6.0 Intel x86.)
>
> Hi,
> I have a table as follows:
>
> create type phone_objtyp as object
> ( location varchar2(10),
> area_code number(3),
> prefix number(3),
> suffix number(4),
> extension number(5))
> /
> CREATE TYPE phone_ntabtyp AS TABLE OF phone_objtyp
> /
> create table users
> (userid number(15) not null,
> username varchar2(15) not null,
> email varchar2(100),
> dob date not null,
> phones_ntab phone_ntabtyp,
> status varchar2(1),
> constraint users_pk1 primary key (userid),
> )
> NESTED TABLE phones_ntab store as user_phones_ntab
> /
>
>
> How do I search for all users with phone numbers
>with a particular area
> code (eg. 408)?
> Please help.
> Thanks
> -Juturi
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

It would look like this:

tkyte_at_8.0> insert into users values

  2  ( 1, 'tom', 'tkyte_at_us.oracle.com', sysdate,
  3     phone_ntabtyp( phone_objtyp( 'work', 703, 000, 0000, 00 ),
  4                    phone_objtyp( 'fax',  703, 111, 0000, 00 ),
  5                    phone_objtyp( 'home', 703, 222, 0000, 00 ) ), 'x' )

  6 /

1 row created.

tkyte_at_8.0> insert into users values

  2  ( 2, 'tom2', 'tkyte_at_us.oracle.com', sysdate,
  3     phone_ntabtyp( phone_objtyp( 'work', 123, 000, 0000, 00 ),
  4                    phone_objtyp( 'fax',  123, 111, 0000, 00 ),
  5                    phone_objtyp( 'home', 123, 222, 0000, 00 ) ), 'x' )

  6 /

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> select *
  2 from users a
  3 where exists ( select NULL

  4                    from THE ( select p.phones_ntab
  5                                 from users p
  6                                where p.userid=a.userid )
  7                   where area_code = 703 )

  8 /

    USERID USERNAME
---------- ---------------
EMAIL

---

DOB

---

PHONES_NTAB(LOCATION, AREA_CODE, PREFIX, SUFFIX, EXTENSION)

---

S
-

         1 tom
tkyte_at_us.oracle.com
09-SEP-99
PHONE_NTABTYP(PHONE_OBJTYP('work', 703, 0, 0, 0), PHONE_OBJTYP('fax', 703, 111, 0, 0), PHONE_OBJTYP(
'home', 703, 222, 0, 0))
x

tkyte_at_8.0>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 09 1999 - 07:41:44 CDT