Re: double outer join

Hans Forbrich wrote:

> What version of the database are you running?
>
> The (+) syntax is older and IIRC does not support the full outer join
> capability as does the new ANSI syntax.
>
> (The new syntax is described for 9.2 at
>
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054625
>
> and more detail of the syntax is at
>
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#SQLRF01702
>
> )
>
> Depending on the version, some of us might give it a go ....
>
> /Hans
>
> Jed wrote:
>
> > My dataset looks like this
> >
> > CIID RESULT NUM
> > A PASS 1
> > A FAIL 2
> > B PASS 1
> > C FAIL 2
> > D PASS 3
> > D FAIL 3
> >
> > I want to query this data and return a dataset that looks like this
> >
> > CIID PASS FAIL
> > A 1 2
> > B 1 0
> > C 0 2
> > D 3 3
> >
> > I tried this (table name is xxx):
> >
> > select pass.ciid, pass.num, fail.num
> > from (select ciid,num from xxx where result='PASS') pass,
> > (select ciid,num from xxx where result='FAIL') fail
> > where pass.ciid=fail.ciid
> >
> > I lose the B and C record
> >
> > When I tried this:
> > select pass.ciid, pass.num, fail.num
> > from (select ciid,num from xxx where result='PASS') pass,
> > (select ciid,num from xxx where result='FAIL') fail
> > where pass.ciid=fail.ciid(+)
> >
> > I still lose the C record
> >
> > Is there a way to do something like a double outer join
> > pass.ciid (+) = fail.ciid (+)
> >
> > When I try, I get the error ORA-01468: a predicate may reference
> > only one outer-joined table

You can do the equivalent of pass.ciid (+) = fail.ciid (+) in Oracle but to do so you must put one outer join in an in-line view and then outer join to the in-line view.

But I already provided the solution to this bunch so why is the dead horse still being beaten?

--
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)