RE: count(*) = 0 and I still need the row

>>>Anyone know why the the date test alters the outer join results?

Dave,

Look at the explain plans below without and with the date clause respectively and you will notice that in the second case the date clause is applied before the "group by" which excludes all rows older than one month (including the "twomonthsago")

Execution Plan -- without date clause

   0 SELECT STATEMENT Optimizer=RULE    1 0 SORT (GROUP BY)

   2    1     MERGE JOIN (OUTER)
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'HP'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'PV'

Execution Plan -- with date clause

   0 SELECT STATEMENT Optimizer=RULE    1 0 SORT (GROUP BY)

   2    1     FILTER
   3    2       MERGE JOIN (OUTER)
   4    3         SORT (JOIN)
   5    4           TABLE ACCESS (FULL) OF 'HP'
   6    3         SORT (JOIN)
   7    6           TABLE ACCESS (FULL) OF 'PV'

hope this resolves your dilemma !! Received on Thu Nov 16 2000 - 16:45:21 CST