outer-join hack that works two ways--why?

A few weeks ago I came up with a really revolting hack using outer joins. Here's an outline of the problem I was trying to solve:

Given a table SH_LIST defined roughly as   SHID VARCHAR2(8)
  SHSTATUS VARCHAR2(4)
  SHDATE DATE For each SHID I needed to extract the maximum SHDATE for the SHSTATUS "CPX", the minimum SHDATE for the SHSTATUS "AO", and the minimum SHDATE altogether into a table SH_SUMMARY defined roughly as   SHID VARCHAR2(8)
  SH_MIN DATE
  CPX_MAX DATE
  AO_MIN DATE Back when I was naive (as opposed to now, of course), I made three passes through SH_LIST:

update sh_summary s
  set cpx_max=(
  select max(shdate)
  from sh_list
  where
    shid=s.shid
  );

another pass with no where clause, and yet another one for for AO_MIN. At this point I should mention that there is *always* an SHSTATUS of CPX, and *usually* one of AO.

After a while I got grossed out by the idea of making two passes, and still didn't want to resort to a PL/SQL procedure of any sort, so I did a rather nifty outer-join hack:

update su_summary s
  set (sh_min, ao_min, cpx_max)=(
  select min(l.shdate), min(ao.shdate), max(cpx.shdate)   from
    sh_list l, sh_list ao, sh_list cpx
  where l.shid = s.shid and
    ao.rowid(+)=l.rowid and ao.shstatus(+)='AO' and     cpx.rowid(+)=l.rowid and cpx.shstatus(+)='CPX'
);

It worked beautifully, and it took only slightly longer to execute than a single, no-joins pass through the table. Then one day my boss told me we no longer needed to calculate SH_MIN. Fine. I changed my query to:

update su_summary s
  set (ao_min, cpx_max)=(
  select min(ao.shdate), max(cpx.shdate)   from
    sh_list l, sh_list ao, sh_list cpx
  where l.shid = s.shid and
    ao.rowid(+)=l.rowid and ao.shstatus(+)='AO' and     cpx.rowid(+)=l.rowid and cpx.shstatus(+)='CPX'
);

Seems reasonable---we're doing the exact same thing, only not returning a value from SH_LIST L. The only problem is that it stopped working. Instead a full column of CPX_MAX, I get very few values interspersed among very many nulls. Same goes for AO_MIN. There seems to be no correlation between those two column's emptiness: I sometimes get AO_MINs but no CPX_MAXs.

So here's my question: why did it quit working? I "solved" the problem by referencing min(l.shdate) in my select statement again, but I'm still curious as to why it happened in the first place.

I'm running Oracle 7.3.3.4.1 on HP-UX 10.20.

Unfortunately, my news-feed is very unreliable here. Although I will attempt to read this group to spot an answer, I would greatly appreciate a reply sent to Carleton.Vaughn (at) bridge (dot) bellsouth (dot) com. I will post, with attributions, any helpful answers I receive via e-mail.

Thanks so much for any help.

--
these are my opinions, not theirs. they are infallible. all hail them. Received on Wed Jul 01 1998 - 14:00:20 CDT