Re: Need help with outer join question ?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 20 Jun 2001 15:06:50 +0200
Message-ID: <9gq75b$867$1@s1.read.news.oleane.net>

"Tom Reid" <thomas_p_reid_at_hotmail.com> a écrit dans le message news: 6dfc26f.0106200218.3be5e759_at_posting.google.com...
> Given the following three tables:-
>
> Price_table
>
> Price Date Price
> ---------- -----
> 16-JUN-01 2.4
> 18-JUN-01 2.3
>
>
> Hilo_table
>
> Price Date Hi_pr Lo_pr
> ----------- ----- -----
> 17-JUN-01 2.45 2.35
> 18-JUN-01 2.35 2.25
>
>
> Volume_table
>
> Volume Date Volume
> ----------- -------
> 16-JUN-01 10000
> 17-JUN-01 10101
>
> I need a query to produce a 4th table as:-
>
> Date Price Hi_pr Lo_pr Volume
> ---- ------- ----- ----- ------
> 16-JUN-01 2.4 10000
> 17-JUN-01 2.45 2.35
> 18-JUN-01 2.3 2.35 2.45
>
>
> I can't seem to get the query I need, I'm presuming some kind of
> outer join is required. Any help would be appreciated

Here's an example of what you can do:

v815> create table price_table (pdate date, price number);
v815> insert into price_table values (to_date('16/06/2001','DD/MM/YYYY'), 2.4);
v815> insert into price_table values (to_date('18/06/2001','DD/MM/YYYY'), 2.3);
v815> create table hilo_table (pdate date, hi_price number, lo_price number);
v815> insert into hilo_table values (to_date('17/06/2001','DD/MM/YYYY'), 2.45, 2.35);
v815> insert into hilo_table values (to_date('18/06/2001','DD/MM/YYYY'), 2.35, 2.25);
v815> create table volume_table (vdate date, volume number);
v815> insert into volume_table values (to_date('16/06/2001','DD/MM/YYYY'), 10000);
v815> insert into volume_table values (to_date('17/06/2001','DD/MM/YYYY'), 10101);
v815> commit;
v815> define from_date = '15/06/2001'
v815> define to_date   = '20/06/2001'
v815> select to_char(o.ldate, 'DD-MON-YY') "Date",
  2         p.price "Price",
  3         hl.hi_price "Hi Price",
  4         hl.lo_price "Lo Price",
  5         v.volume "Volume"
  6  from price_table p,
  7       hilo_table hl,
  8       volume_table v,
  9       ( select to_date('&from_date','DD/MM/YYYY')+rownum-1 ldate
 10         from all_objects
 11         where to_date('&from_date','DD/MM/YYYY')+rownum-1
 12               between to_date('&from_date','DD/MM/YYYY')
 13                   and to_date('&to_date','DD/MM/YYYY')) o

 14 where p.pdate (+) = o.ldate
 15 and hl.pdate (+) = o.ldate
 16 and v.vdate (+) = o.ldate
 17 order by 1
 18 /

Date Price Hi Price Lo Price Volume

--------- ---------- ---------- ---------- ----------
15-JUN-01
16-JUN-01        2.4                            10000
17-JUN-01                  2.45       2.35      10101
18-JUN-01        2.3       2.35       2.25
19-JUN-01
20-JUN-01

6 rows selected.

--
Have a nice day
Michel

Received on Wed Jun 20 2001 - 08:06:50 CDT