Re: Outer Join and Group By
Date: 1995/08/04
Message-ID: <3vtg92$kuk_at_ixnews2.ix.netcom.com>#1/1
In <3vr0s7$lv3_at_ixnews6.ix.netcom.com> pranav_at_ix.netcom.com
(Gopalakrishnan Hariharan ) writes:
>
>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
>
This solution won't use index if there is one index on Outer_month. Try This
select IN_NUMBER, IN_NAME, SUM(NVL(OUT_AMOUNT,0))
from inner, outer
where IN_NUMBER = OUT_NUMBER (+)
and (OUTER_MONTH >= '9504'or OUT_MONTH IS NULL)
group by IN_NUMBER, IN_NAME
Hope this Helps.
Swapan Jha
Sat
jhas_at_ix.netcom.com Received on Fri Aug 04 1995 - 00:00:00 CEST