Re: double outer join

For the sake of amusement, I have provided a additional select statements at the bottom that provide the same answers on the sample data set. These were tested in 9.2 and reinforce the reason why I asked about the version number - I do not believe they will work under 8i.

The results from the first two diverge wildly from Daniel's solution on larger data sets where uniqueness on some columns is not enforced. I believe the third one always provides the same answer as Daniel's solution, but I have not taken the time to verify this mathematically. To mis-quote some of my profs from years ago - "I leave it as a student to identify the situations in which to use each variant."

I submit these as a practical example and reinforcement why several of us are generally hesitant to provide 'final' solutions - the solution may work in a test environment but still may not be correct. Blindly accepting someone else's solution can cause unexpected results in a production environment.

/Hans

Daniel Morgan wrote:

> > Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EE8E517.1785C15_at_exxesolutions.com>...
> > > 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
> > >
> > > This is classroom homework so I'll give you a hint rather than the
> > > answer.
> > >
> > > You are on the right track but think about NVL and how you might use it.

>
> And miraculously he needs to know this at a time the corresponds with the end
> of the semester? Fascinating.
>
> Well since you guys are so interested in the answer rather than an education
> I will violate my basic principles and give it to you in the hope that you might
> learn something. Perhaps starting with not posting with language best left on
> the school yard when graduating from middle-school at age 14.
>
> CREATE TABLE test (
>   ciid   VARCHAR2(1),
>   result VARCHAR2(4),
>   num    NUMBER(1));
>
> INSERT INTO test  VALUES ('A', 'PASS', 1);
> INSERT INTO test  VALUES ('A', 'FAIL', 2);
> INSERT INTO test  VALUES ('B', 'PASS', 1);
> INSERT INTO test  VALUES ('C', 'FAIL', 2);
> INSERT INTO test  VALUES ('D', 'PASS', 3);
> INSERT INTO test  VALUES ('D', 'FAIL', 3);
>
> SELECT ciid, SUM(pass) PASS, SUM(fail) FAIL
> FROM (
>    SELECT ciid, DECODE(result, 'PASS', NUM, 0) PASS,
>                 DECODE(result, 'FAIL', NUM, 0) FAIL
>    FROM test)
> GROUP BY ciid
> /
>
> The entire time I spent doing this was less than four minutes including creating the table
> and writing the insert statements. Which says much about you, and your DBA if such
> a person exists which I doubt.
>
> I would advise any and all potential employers to make a note that these two, if it is even two,
> are unworthy of employment. And if you were in my class at the University of Washington
> you would FAIL!
>
> --
> 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)

(Note to cut & pasters- my test table had col ccid & I hope I corrected all occurances to ciid properly).

Variant 1 & 2 are basically the same and use SQL '99 and full outer join ...

select ciid, nvl(passnum,0) pass, nvl(failnum,0) fail   from (
 select ciid, pass, passnum, fail, failnum    from ( select ciid, result pass, num passnum from test where result='PASS')   full outer join

        ( select ciid, result fail, num failnum from test where result='FAIL')   using ( ciid )
 )
  order by ciid;

select ciid, pass, fail
  from (
 select ciid, nvl(pass,0) pass, nvl(fail,0) fail    from ( select ciid, num pass from test where result='PASS')   full outer join

        ( select ciid, num fail from test where result='FAIL')   using ( ciid )
 )
  order by ciid;

Variant 3 mimics Daniel's solution but using SQL '99 constructs and full outer join.

select ciid, sum(pass), sum(fail)
  from (
 select ciid, nvl(pass,0) pass, nvl(fail,0) fail    from ( select ciid, num pass from test where result='PASS')   full outer join

        ( select ciid, num fail from test where result='FAIL')   using ( ciid )
  order by ciid
 )
  group by ciid; Received on Sat Jun 14 2003 - 20:34:03 CDT