Outer join returns incorrect results when using sub-selects
Date: 26 Sep 2003 06:45:46 -0700
Message-ID: <57c2930a.0309260545.3eef1732_at_posting.google.com>
I'm seeing a very strange problem with outer joins.
The example below replicates the problem:
create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);
I want to be able to run a query that generates the resultset:
dim1, sum(stat1), sum(stat2)
I was planning to use the following sql:
select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
When I run this. Instead of the 4 rows I was expecting I get back:
dim1,stat1,stat2
insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);
insert into data1 values (3,3);
insert into data1 values (6,3);
insert into data1 values (7,3);
insert into data2 values (2,1);
insert into data2 values (2,2);
insert into data2 values (3,2);
insert into data2 values (4,3);
insert into data2 values (5,3);
1,3,NULL
2,4,3
3,3,2
4,NULL,3
5,NULL,3
6,3,NULL
7,3,NULL
(
select dim1, sum(stat1) stat1 from data1 group by dim1
) s1
full outer join
(
select dim1, sum(stat2) stat2 from data2 group by dim1
) s2 on s1.dim1=s2.dim1
order by dim1;
1,3,NULL
2,4,3
3,3,2
4,NULL,NULL
5,NULL,NULL
6,3,NULL
7,3,NULL
The values for stat2 seem to be getting lost when there is no value for stat1.
Anyone have any ideas as to what is going on? Am I doing something stupid? Received on Fri Sep 26 2003 - 15:45:46 CEST