Re: double outer join

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Wed, 18 Jun 2003 18:55:03 -0700
Message-ID: <3EF117F7.B3E0A179@exxesolutions.com>

Joel Garry wrote:

> djedziniak_at_hotmail.com (Jed) wrote in message news:<d1233510.0306171216.7bdc8b05_at_posting.google.com>...
> > Daniel wrote:
> >
> > > Understandable.
> > >
> > > I'm actually someone sorry I helped them. They certainly didn't deserve it.
> >
> > Perhaps I was a little harsh, but you did insult me by calling me a
> > student and treating me like a child. I have no idea who you are
> > (though my DBA informs me you are published - whatever that's supposed
> > to mean). Your opinion of my SQL skills is of no consequence, since
> > this is not my field of specialty. I would never take one of your
> > classes, knowing what little I do about your personality, so whether
> > or not I would fail is irrelevant.
> >
> > It is a profitable thing, if one is wise, to seem foolish. -Aeschylus
> >
> >
> >
> >
> > Anyway...
> >
> > Thanks to all those who attempted to help. Here is where I am going
> > with this.
> >
> > Say the data comes from 2 different tables.
> >
> > I can still get the result this way,
> >
> > SELECT
> > ciid,
> > SUM(pass) PASS,
> > SUM(fail) FAIL
> > FROM (
> > SELECT
> > ciid,
> > DECODE(result, 'PASS', NUM, 0) PASS,
> > 0 FAIL
> > FROM test
> > UNION
> > SELECT
> > CIID,
> > 0 PASS,
> > DECODE(RESULT,'FAIL',NUM,0) FAIL
> > FROM TEST2)
> >
> > GROUP BY ciid
> >
> >
> >
> > but there must be a cleaner (read faster) way. I have heard of the
> > FULL OUTER JOIN in 9i, but unfortunately, we are still on 8.1.7

>

> Various bugs in some versions of 8.1.7, some having to do with CBO.
> So it could make a difference which patch set.

>

> I'm still a bit miffed at having spent some time recently struggling
> with an inline update on 8.1.7.2, where there was a known bug, and the
> workaround was RBO... and I'm using RBO. And I get differing results
> running it at different times.

>

> So even if outer join bugs are listed as fixed in the "Wrong results"
> part of the bug fix list, one just never knows...

>

> jg
> --
> @home.com is bogus.
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=120613.1

The current version of 8i is 8.1.7.4. And as I've had a copy of 8i since before its official release ... I can say, with some certainty, that I have never observed an outer-join but that would affect the solution I posted.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed Jun 18 2003 - 20:55:03 CDT