Outer Join and Group By

From: Simon Thompson <scthomp_at_ibm.net>
Date: 1995/08/03
Message-ID: <3vq6bb$k57_at_news-s01.ca.us.ibm.net>#1/1

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 Received on Thu Aug 03 1995 - 00:00:00 CEST