Re: Outer join returns incorrect results when using sub-selects

chris.greening_at_actix.com (Chris Greening) wrote in message news:<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);
>
> 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);
>
> I want to be able to run a query that generates the resultset:
>
> dim1, sum(stat1), sum(stat2)
> 1,3,NULL
> 2,4,3
> 3,3,2
> 4,NULL,3
> 5,NULL,3
> 6,3,NULL
> 7,3,NULL
>
> I was planning to use the following sql:
>
> select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
> (
> 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;
>
> When I run this. Instead of the 4 rows I was expecting I get back:
>
> dim1,stat1,stat2
> 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?

I don't see the problem, but I did notice the nvl() function. A NULL shouldn't occur in the join column (at least not with the data you presented) so you should just list the key:

select s1.dim1, stat1, stat2 from

I doubt that would fix the other columns, but this is going to be one of those "intuitive obvious" solutions, i.e., once you know the answer, you realize it was "intuitive obvious" 8^) Received on Fri Sep 26 2003 - 20:35:17 CEST