Re: double outer join

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Fri, 13 Jun 2003 08:37:29 -0700
Message-ID: <3EE9EFB9.4F823483@exxesolutions.com>

scott wrote:

> Guys this wasn't homework, I know JED and he's under the gun to get
> some things done at work so I suggested he post his questions here.
> It would be great if you could give him a hand instead of treating him
> like some college kid.
>
> Thanks
> Scott
>
> 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)

Received on Fri Jun 13 2003 - 10:37:29 CDT