| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Q:Oracle8- relating nested table with surrounding row
A copy of this was sent to may_at_informatik.uni-freiburg.de (Wolfgang May)
(if that email address didn't require changing)
On 3 Jul 1998 14:41:36 GMT, you wrote:
>Hi,
>
>I have a problem with correlating entries of a table with entries
>of a nested table inside this table:
>
>CREATE TYPE language_TY AS OBJECT
> (language VARCHAR2(20),
> percent NUMBER );
>/
>
>CREATE TYPE languages_coll AS
> TABLE OF language_TY;
>/
>
>CREATE TABLE Country
> (Name VARCHAR2(32),
> C_ID CHAR(4),
> Languages languages_coll)
> NESTED TABLE Languages STORE AS Lang_nest_tab ;
>
>INSERT INTO Country VALUES ('Switzerland','CH',
> languages_coll
> (language_TY('german',63.7),
> language_TY('french',19.2),
> language_TY('italian',7.6),
> language_TY('romansch',0.6)));
>INSERT INTO Country VALUES ('Belgium','B',
> languages_coll
> (language_TY('flamish',56),
> language_TY('french',32),
> language_TY('german',1),
>
>Thus, the table looks as follows:
>
>CH,german
> french
> italian
> romansch
>B,flamish
> french
> german
>
>Now I want to formulate a query correlating the outer table with its
>inner table, giving all pairs (country,language) s.t. language is
>spoken in country, i.e. the output
>
You cannot. What you have in your database is 1 table with 2 rows. What you are asking for is 7 rows. You want both the object view and the relational view of the data simultaneously (we can most certainly achive this, just not the way you are trying -- I'll show you how to achive this in a minute...)
In order to get the result set below, you must have a table somewhere with 7 rows in it. Conceptually what you have above is a table with 2 rows -- the first row has four Virtual Tables associated with it and the second row has 3 virtual tables. Your query below would have the virtual table change for each row in the query -- every row that comes from C would materialize the correct table D to join to. That cannot be done.
>CH,german
>CH,french
>CH,italian
>CH,romansch
>B,flamish
>B,french
>B,german
>
>The following does not work, I tried several other variants, but I
>do not find an example in any documentation:
>
>select C.C_ID, language from
>Country C, THE (SELECT Languages from Country D where
> D.Name=C.Name);
> ^
>Function not implemented =:((
>
The approach to take, when you want both the 'old fashioned' relational view of the data and the new object view of the data is to store the data relationally and present it in an object view. For your example, this would look like:
create table country_tbl
( name varchar2(32),
c_id varchar2(4) primary key
)
/
create table languages_tbl
( c_id varchar2(4) references country_tbl(c_id),
language varchar2(20),
percent number,
primary key(c_id,language)
)
/
CREATE TYPE language_TY AS OBJECT
(language VARCHAR2(20),
percent NUMBER );
/
CREATE TYPE languages_coll AS
TABLE OF language_TY;
/
create type country_ty as object
( name varchar2(32),
c_id varchar2(4),
languages languages_coll
)
/
insert into country_tbl values ( 'Switzerland', 'CH' ); insert into country_tbl values ( 'Begium', 'B' );
insert into languages_tbl values ( 'CH', 'german', 63.7 );
insert into languages_tbl values ( 'CH', 'french', 19.2 );
insert into languages_tbl values ( 'CH', 'italian', 7.6 );
insert into languages_tbl values ( 'CH', 'ramansch', 0.6 );
insert into languages_tbl values ('B', 'flamish', 56 );
insert into languages_tbl values ('B', 'french', 32 );
insert into languages_tbl values ('B', 'german', 1 );
create view country of country_ty with OBJECT OID(c_id)
as
select country_tbl.name, country_tbl.c_id,
cast ( multiset( select language, percent
from languages_tbl
where country_tbl.c_id = languages_tbl.c_id )
as languages_coll )
So,
select * from country
returns you the object relational view of the data with nested tables (eg:
SQL> select * from country
2 /
NAME C_ID LANGUAGES(LANGUAGE, PERCENT)
------------------------------ ---- -------------------------------------
Switzerland CH LANGUAGES_COLL(LANGUAGE_TY('french', 19),
LANGUAGE_TY('german', 64), LANGUAGE_TY('italian', 8), LANGUAGE_TY('ramansch',
1))
Begium B LANGUAGES_COLL(LANGUAGE_TY('flamish', 56),
LANGUAGE_TY('french', 32), LANGUAGE_TY('german', 1))
select * from country_tbl c, languages_tbl l where c.c_id = l.c_id
gives you the flat relational view you desire.
NAME C_ID C_ID LANGUAGE PERCENT ------------------------------ ---- ---- -------------------- ---------- Switzerland CH CH german 63.7 Switzerland CH CH french 19.2 Switzerland CH CH italian 7.6 Switzerland CH CH ramansch .6 Begium B B flamish 56 Begium B B french 32 Begium B B german 1
>THX
>
>Wolfgang
>
>-- -----------------------------------------------------------------
>Wolfgang May E-mail: may_at_informatik.uni-freiburg.de
>Universitaet Freiburg http://www.informatik.uni-freiburg.de/~may/
>Institut fuer Informatik D-79110 Freiburg / Germany
>Lehrstuhl fuer Datenbanken und Informationssysteme
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jul 03 1998 - 13:50:35 CDT
 |
 |