Q:Oracle8- relating nested table with surrounding row

From: Wolfgang May <may_at_informatik.uni-freiburg.de>
Date: 3 Jul 1998 14:41:36 GMT
Message-ID: <6niqj0$fdj$2@n.ruf.uni-freiburg.de>

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

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 =:((

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

Received on Fri Jul 03 1998 - 09:41:36 CDT