Re: Outer Join and Group By

In article <3vq6bb$k57_at_news-s01.ca.us.ibm.net> scthomp_at_ibm.net (Simon Thompson) writes:

   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;

The reason that you did not get any rows returned where the were no outer rows for an inner row is the OUT_MONTH >= '9504' constraint. For INNER rows that have no corresponding OUTER row, OUT_MONTH will be NULL and NULL causes the constraint to fail. Try something like this:

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

-- 
==============================
Mark Grand