If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > syntax help

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 11-02-03, 17:24
minnie1 minnie1 is offline
Registered User
 
Join Date: Nov 2003
Posts: 2
syntax help

Can anyone help me out with a complicated(for me) syntax.

I have created several types and a table. Code is below.

create type person_type;
/
show errors;

create type person_ref_ntabtype as
table of ref person_type;
/
show errors;

create or replace type project_type as object (
project_id varchar2(10),
project_description varchar2(30),
people person_ref_ntabtype
);
/
show errors;


create or replace type person_type as object (
id varchar2(10),
firstname VARCHAR2(15),
lastname VARCHAR2(15)
);
/
show errors;

create table project of project_type (
primary key (project_id)
)
nested table people store as people_ntab;


create table person of person_type (
primary key (id)
);
INSERT INTO PERSON
VALUES ( person_type('JB234','James', 'Brown'));
INSERT INTO PERSON
VALUES ( person_type('SW123','Sarah', 'White'));
INSERT INTO PERSON
VALUES ( person_type('GB245','George','Black'));
INSERT INTO PERSON
VALUES ( person_type('GH237','Goldy','Horn' ));
INSERT INTO PERSON
VALUES ( person_type('FG246','Fred','Greene'));



insert into project values (
project_type('PM123','Townhouse', person_ref_ntabtype()));
insert into project values (
project_type('PM245','Environmentally Sound home', person_ref_ntabtype()));
insert into project values (
project_type('PM289','Dual House',person_ref_ntabtype()));

update project
set people =
person_ref_ntabtype(
(select ref(s) from person s where id='JB234'),
(select ref(s) from person s where id='SW123'),
(select ref(s) from person s where id='FG246'))
where project_id='PM123';

update project
set people =
person_ref_ntabtype(
(select ref(s) from person s where id='JB234'),
(select ref(s) from person s where id='FG246'))
where project_id='PM245';

update project
set people =
person_ref_ntabtype()
where project_id='PM289';


I now need to be able to access the reference within the nested table called people.
the closes that I can manage is

select p.project_id, p.description, p,people
from project p

I know that I need to deref the nested table, but cannot incorporate the deref syntax into this statement. Can you help?



Thanks in advance
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On