Re: double outer join

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
Received on Fri Jun 13 2003 - 18:56:39 CDT