Re: Need help with outer join question ?

From: MarkyG <markg_at_mymail.tm>
Date: 20 Jun 2001 06:10:27 -0700
Message-ID: <ab87195e.0106200510.2def7127@posting.google.com>

After solving this for you, mine is a nice cool cider!

Because the data is comming from 3 seperate tables with different field names, a bit of aliasing is needed to get required results.

To create a table based on the resilts just do

create table ??? as
 <sql_statement>

Example (including create table script) provided.

---

drop table price_table;
drop table hilo_table;
drop table volume_table;
create table price_table
(price_date date,
price number);
insert into price_table values(to_date('16-jun-2001','dd-mon-yyyy'), 2.4);
insert into price_table values(to_date('18-jun-2001','dd-mon-yyyy'), 2.3);

create table hilo_table
(price_date date,
hi_pr number,
lo_pr number);
insert into hilo_table values(to_date('17-jun-2001','dd-mon-yyyy'), 2.45, 2.35);
insert into hilo_table values(to_date('18-jun-2001','dd-mon-yyyy'), 2.35, 2.25);

create table volume_table
(volume_date date,
volume number);
insert into volume_table values(to_date('16-jun-2001','dd-mon-yyyy'), 10000);
insert into volume_table values(to_date('17-jun-2001','dd-mon-yyyy'), 10001);

SELECT DISTINCT
 TRUNC(datestamp) datestamp,

 SUM(DECODE(price,NULL, NULL, price)) price,
 SUM(DECODE(hi_pr, NULL, NULL, hi_pr)) hi_pr,
 SUM(DECODE(lo_pr, NULL, NULL, lo_pr)) lo_pr,
 SUM(DECODE(volume, NULL, NULL, volume)) volume

FROM
   (
 SELECT price_date datestamp, price, 0 hi_pr, 0 lo_pr, 0 volume FROM price_table
 UNION
 SELECT price_date datestamp, 0 price, hi_pr, lo_pr, 0 volume FROM hilo_table
 UNION
 SELECT volume_date datestamp, 0 price, 0 hi_pr, 0 lo_pr, volume FROM volume_table

   ) pr
GROUP BY
 TRUNC(datestamp)

SQL> / DATESTAMP PRICE HI_PR LO_PR VOLUME

--------- ---------- ---------- ---------- ----------
16-JUN-01        2.4          0          0      10000
17-JUN-01          0       2.45       2.35      10001
18-JUN-01        2.3       2.35       2.25          0

=========================================

HTH Mark

thomas_p_reid_at_hotmail.com (Tom Reid) wrote in 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
Received on Wed Jun 20 2001 - 08:10:27 CDT