Re: Outer Join and Group By

In <3vq6bb$k57_at_news-s01.ca.us.ibm.net> scthomp_at_ibm.net (Simon Thompson) writes:
>
>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
>

Most likely possibility is that

   and OUTER_MONTH >= '9504'
failed because of a comparison involving NULL

changing the query to

   and nvl(OUTER_MONTH, '9504') >= '9504' might help
alternate (not tried)

   and OUTER_MONTH >= '9504' (+)

there are some restrictions on using outer joins. Will the second case violate that. I do not know.

hope this helps
krish Received on Thu Aug 03 1995 - 00:00:00 CEST