Re: Outer join on date field

Look at your table carefully. The restrictions you placed on the h.meter field eliminate the case you are trying to display.

Oracle cannot do full outer joins if I interpret your question directly. However, by doing two different outer joins and using the union operator you can create the output I believe you are interested in:

Select .... from a,b where a.f1 = b.f1(+) union
select ... from a,b where a.f1(+) = b.f1

Christopher Weiss
Professional Services Division
Compuware Corporation

Peter Laursen < ptl_at_edbgruppen.dk> wrote in message news:01bed8f2$276b20d0$2c289a0a_at_apollo...
> What am I missing in the select stmt below? My concern is the last (+) I
> cant make the outer join work.
> Also how do I construct a full outer join?
>
> Thanks
> Peter
>
> create table testmeter(m int primary key);
>
> insert into testmeter values(108);
> insert into testmeter values(109);
>
> create table testctrl(
> recnum int primary key,
> sampletime date,
> value int,
> meter int references testmeter);
>
> insert into testctrl values(1, to_date('25-05-98 00:15:00', 'dd-mm-yy
> hh24:mi:ss'), 47, 108);
> insert into testctrl values(2, to_date('25-05-98 00:30:00', 'dd-mm-yy
> hh24:mi:ss'), 56, 108);
>
> insert into testctrl values(3, to_date('25-05-98 00:15:00', 'dd-mm-yy
> hh24:mi:ss'), 47, 109);
> insert into testctrl values(4, to_date('25-05-98 00:45:00', 'dd-mm-yy
> hh24:mi:ss'), 65, 109);
>
> SQL> select to_char(k.sampletime, 'dd-mm-yy hh24:mi:ss'),
> 2 to_char(h.sampletime, 'dd-mm-yy hh24:mi:ss'),
> 3 h.value h_value, k.value k_value,
> 4 abs(nvl(h.value, 0) - nvl(k.value, 0)) diff
> 5 from testctrl h, testctrl k
> 6 where k.meter = 108
> 7 and h.meter = 109
> 8 and h.sampletime between to_date('25-05-98 00:15:00', 'dd-mm-yy
> hh24:mi:ss')
> 9 and to_date('25-05-98 01:00:00', 'dd-mm-yy
> hh24:mi:ss')
> 10 and h.sampletime = k.sampletime (+);
>
> TO_CHAR(K.SAMPLET TO_CHAR(H.SAMPLET H_VALUE K_VALUE DIFF
>
> ----------------- ----------------- --------- --------- ---------
>
> 25-05-98 00:15:00 25-05-98 00:15:00 47
47
> 0
>
>
> Here i would expect the select to output another row with h_value=56 and
> k_value=NULL
>
Received on Wed Jul 28 1999 - 08:03:29 CDT