Re: double outer join

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