Re: Outer Join and Group By

scthomp_at_ibm.net (Simon Thompson) wrote:

>I have a problem where an outer join to a table did not get all the records I
>expected. In this case, the field from the outer table is a summary field,
>but on a subset of the records. (I hope I'm getting the inner and outer round
>the correct way :-) )
 

>The inner table is similar to this:
 

> IN_NUMBER NUMBER
> IN_NAME VARCHAR2
 
>Sample records:
> 1 ABC Limited
> 2 XYZ Corp
 

>The outer table is similar to this:
 

> OUT_NUMBER NUMBER
> OUT_MONTH CHAR4 (yymm)
> OUT_AMOUNT NUMBER
 
>sample records:
> 1 9503 1,000
> 1 9504 1,500
> 1 9505 2,000
 

>The inner table has records where there are no outer records. The outer table
>can have several records for each inner record (Obviously, one per month).
 

>I did a query like this:
 

> select IN_NUMBER, IN_NAME, sum(OUT_AMOUNT)
> from INNER, OUTER
> where IN_NUMBER = OUT_NUMBER (+)
> and OUT_MONTH >= '9504'
> group by IN_NUMBER, IN_NAME;
 

>I did not get the inner records that did not have any records in the outer
>table. I got:
 

> 1 ABC Limited 3,500
 

>I did not get, as I expected:
 

> 1 ABC Limited 3,500
> 2 XYZ Corp
 

>I changed the query to:
 

> select IN_NUMBER, IN_NAME, sum(OUT_AMOUNT)
> from INNER, OUTER
> where IN_NUMBER = OUT_NUMBER (+)
> group by IN_NUMBER, IN_NAME;
 

>and got the results I expected:
 

> 1 ABC Limited 4,500
> 2 XYZ Corp

>, but not the summary total I required.
 

>Should I have got the results I expected? If not, why not?
 

>I solved the problem using a temporary view.
 

>TIA.
 
>+---------------------------
>| Simon Thompson
>| Christchurch
>| New Zea
>land

In your fisrt example, I think the problem in that you do not handle the null values returned by the outer join (null is NOT >= 9504)

Try the folowing :

	select IN_NUMBER, IN_NAME, sum(OUT_AMOUNT)
	  from INNER, OUTER
	  where IN_NUMBER = OUT_NUMBER (+)
***
	    and NV L(OUT_MONTH,'9504') >= '9504'
                       --------                          -----------
 ***
	  group by IN_NUMBER, IN_NAME;

In the second example, total is wrong because of you don't discard the record concerning month 9503

Hope this can help Received on Fri Aug 04 1995 - 00:00:00 CEST