SQL-statements , outer-joins

From: Dr P.G. Sjoerdsma <pgs1002_at_cus.cam.ac.uk>
Date: Fri, 25 Feb 1994 16:16:56 GMT
Message-ID: <PGS1002.94Feb25161643_at_bootes.cus.cam.ac.uk>

If you decide to send e-mail, please use: pgs1002_at_esc.cam.ac.uk

I got the following tables:
/* only the relevant pieces are shown */

create table core
(
 unique_id integer not null primary key,  ..
);

create table contents
(

 unique_id          integer not null primary key,
 core               integer not null references core(unique_id),
 name               varchar2(50)

 );

create table plane
(

 unique_id          integer not null primary key,
 core               integer not null references core(unique_id),
 depth              number not null

 );

create table observation
(

 unique_id           integer not null primary key,
 plane               integer not null references plane(unique_id),
 contents            integer not null references contents(unique_id),
 data                number  not null

 );

These tables store data from marine cores, drilled from the sea-floor, the idea behind it is:

contents A B C D

---

core
  |
  |
----->plane 1 3.5 0.001
  |
  |
----->plane 2 11.5 0.01
  |
  |
----->plane 3 1 5.1 0.87

At certain levels, called planes samples are taken and measurements (observations) are made. These maybe NULL (nothing measured) but NULL values are prohibited in the observation-table. The reason for that is simple: either you do it completely or not at all. If I would maintain NULL values what would happen if I were to add contents G-Z with a new set of planes for the same core.

What I would like to do on a regular basis is to 'export' these cores and planes and observations to a ftp-directory for anonymous-ftp use. the exported table would look like this:

file core.5

---

depth A B C D

..       x        x   x
..       x   x
..                x   x
..                x   x

were x are measurments. I want those NULL-values. I have to use an outer-join and I have got this far:

SQL> create view bv1 as select data,plane from observation where   2 plane in (select unique_id from plane where core = 1) and contents = 1; SQL> create view bv2 as select data, plane from observation where   plane in (select unique_id from plane where core = 1) and contents = 2;

SQL> create view av as select unique_id, depth from plane where core =1; SQL> describe av;
 Name Null? Type

• -------- ---- UNIQUE_ID NOT NULL NUMBER(38) DEPTH NOT NULL NUMBER

SQL> describe bv1;
 Name Null? Type

• -------- ---- DATA NOT NULL NUMBER PLANE NOT NULL NUMBER(38)

SQL> select data, depth from
  2 bv1, av where
  3 av.unique_id = bv1.plane(+);

SQL> select O1.data, O2.data, P.depth from

     bv1 O1, bv2 O2, av P where     
     P.unique_id = O1.plane(+) and
     P.unique_id = O2.plane(+);

But after this I'm stuck:

1. Is there a way to do it without having to create a view for each column (like in the second select, with the two outer-joins)
2. Is there an easier way at all, in the end there will be: 10000 cores with around 10 contents and a 500-1000 planes.

I'm using oratcl so I can construct sql-statements dynamically

Thanks

Paul

--
                                 ___
                                (o o) 
-----------------------------ooO-(_)-Ooo-----------------------------
        Paul Sjoerdsma, Godwin Laboratory, Free School Lane,
                     Cambridge CB2 3RS England
       fax:   (44) (0)223-334871, email: pgs1002_at_cus.cam.ac.uk
---------------------------------------------------------------------

Received on Fri Feb 25 1994 - 17:16:56 CET