Re: How to use SELECT with this kind of table?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 10 Jan 2000 09:08:44 -0500
Message-ID: <ntpj7s0c8jtodpd9rutn8h9ttl7n8ufu7b@4ax.com>

A copy of this was sent to ppakorn_at_my-deja.com (if that email address didn't require changing) On Sun, 09 Jan 2000 15:18:56 GMT, you wrote:

>Dear all users
>
>I created a table as below
>
>create type name_o as object (
>first varchar2(10),
>middle varchar2(10),
>last varchar2(15));
>/
>
>create type address_o as object (
>street varchar2(15),
>city varchar2(15),
>state char(2),
>zip char(5));
>/
>
>create table student (
>stuid char(11) not null,
>stuname name_o,
>stuaddr address_o,
>stutel varchar2(11),
>primary key (stuid));
>
>then I insert some rows to this table
>
>insert into student values (
>'000-00-0001',
>name_o('Steve', 'A', 'Sigler'),
>address_o('210 E.Main', 'Springfield', 'IL', '61220'),
> '452-4205750');
>
>insert into student values (
>'000-00-0002',
>name_o('Joe', 'J', 'Livinston'),
>address_o('600 W.Mill St', 'Carbondle', 'IL', '62901'),
> '618-4576544');
>
>insert into student values ('
>000-00-0003',
>name_o('Thomas', 'J', 'Young'),
>address_o('1500 W.Cherry', 'San Francisco', 'CA', '24101'),
> '235-5754864');
>
>Could anyone tell me how can I do SELECT to bring up student first
>names who live in Illinois (IL)? I have tried many ways, but didn't
>succeeded. Please help me.
>
>

Just like we did to select the first name in the other example:

ops$tkyte_at_8.0> select S.stuname.first
  2 from student S
  3 where S.stuaddr.state = 'IL'
  4 /

STUNAME.FI

---

Steve
Joe

You must use the table qualifier (S in my example) to dereference the discrete elements within the object type.

>
>
>Thank you in advance
>Pak
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.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 Mon Jan 10 2000 - 08:08:44 CST