Re: Need help with outer join question ?

From: Isaac Blank <izblank_at_yahoo.com>
Date: Wed, 20 Jun 2001 09:41:09 -0700
Message-ID: <2m4Y6.7$Mk4.22709@news.pacbell.net>

A variation of this will only produce dates that are in either of the three tables:

select to_char(o.ldate, 'DD-MON-YY') "Date",

         p.price "Price",
         hl.hi_price "Hi Price",
         hl.lo_price "Lo Price",
         v.volume "Volume"
      from price_table p,
       hilo_table hl,
       volume_table v,
       ( select pdate from price_table
        UNION
        select pdate from hilo_table
        UNION
        select vdate from volume_table ) o

where p.pdate (+) = o.ldate

    and hl.pdate (+) = o.ldate
    and v.vdate (+) = o.ldate
  order by 1

"Michel Cadot" <micadot_at_netcourrier.com> wrote in message news:9gq75b$867$1_at_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 - 11:41:09 CDT