Re: Can I do a conditional join?

From: Michael Sallwasser <msallwas_at_world.nad.northrop.com>
Date: Mon, 20 Jun 1994 20:16:31 GMT
Message-ID: <CrppnJ.EG6_at_gremlin.nrtc.northrop.com>


In article <2tkk94$e1o_at_k2.San-Jose.ate.slb.com> jgg_at_waldo.corte-madera.geoquest.slb.com (John Gillespie) writes:
>In article <2ti0jr$e1n_at_cherokee.advtech.uswest.com>, diane_at_advtech.uswest.com (Diane Diesh) writes:
>|> I am trying to create a view which consists of a join on several tables.
>|> I am wondering if it is possible to allow a column in the view to simply
>|> take a null value if the join conditions which populate that column
>|> fail. For example:
>|>
>|> TABLE: (id, name)
>|> (1, 'Stanford')
>|> (2, 'MIT')
>|> (3, 'UC Berkeley')
>|>
>|> TABLE: PERSON (id, name)
>|> (1, 'Mary Smith')
>|> (2, 'John Doe')
>|> (3, 'Susan Miller')
>|>
>|> TABLE: EDUCATION (person_id, university_id, type, discipline, year)
>|> (1, 2, 'BS', 'Computer Science', '1988')
>|> (1, 2, 'MS', 'Electrical Engineering', '1990')
>|> (2, 1, 'BS', 'Physics', '1985')
>|> (2, 3, 'PhD', 'Physics', '1989')
>|>
>|> Here's the view I'd like (I probably don't have this syntax exactly
>|> right, but you get the idea):
>|>
>|> CREATE VIEW PERSON_EDUCATION
>|> (name, undergraduate, graduate, doctoral) as
>|> select
>|> p.name,
>|> uu.name + eu.type + eu.discipline + eu.year,
>|> ug.name + eg.type + eg.discipline + eg.year,
>|> ud.name + ed.type + ed.discipline + ed.year
>|> from
>|> PERSON p,
>|> EDUCATION eu,
>|> EDUCATION eg,
>|> EDUCATION ed,
>|> UNIVERSITY uu,
>|> UNIVERSITY ug,
>|> UNIVERSITY ud
>|> where
>|> p.id = eu.person_id and
>|> p.id = eg.person_id and
>|> p.id = ed.person_id and
>|> uu.id = eu.university_id and
>|> ug.id = eg.university_id and
>|> ud.id = ed.university_id
>|>
>|> The problem is, this view is populated only for persons who have
>|> undergraduate, graduate, and doctoral degrees. I would like to
>|> describe the view such that those columns would be null if there
>|> is no corresponding row in the EDUCATION table. Can it be done?
>|>
>|> E-mail preferred, thanks in advance...
>|> --
>|> Diane Diesh
>|> diane_at_advtech.uswest.com
>
>Here's an outer join syntax that SHOULD work, but see below %^(:
>
> CREATE VIEW PERSON_EDUCATION
> (name, undergraduate, graduate, doctoral) as
>select
> p.name,
> uu.name ||' '|| eu.type ||' '|| eu.discipline ||' '|| eu.year,
> ug.name ||' '|| eg.type ||' '|| eg.discipline ||' '|| eg.year,
> ud.name ||' '|| ed.type ||' '|| ed.discipline ||' '|| ed.year
>from
> PERSON p,
> EDUCATION eu,
> EDUCATION eg,
> EDUCATION ed,
> UNIVERSITY uu,
> UNIVERSITY ug,
> UNIVERSITY ud
> where
> (p.id = eu.person_id(+) and uu.id = eu.university_id and eu.type='BS') or
> (p.id = eg.person_id(+) and ug.id = eg.university_id and eg.type='MS') or
> (p.id = ed.person_id(+) and ud.id = ed.university_id and ed.type='PhD');
>
>Here's Oracle's response:
>ORA-01719: outer join operator (+) not allowed in operand of OR or IN
>
>So you may be stuck with having to create the answer in a temporary table
>or through PL/SQL...

Actually you can still solve the problem without PL/SQL or a temporary table.

  1. Create a three views that join the Education and University tables, one for undergraduate, one for graduate, and one for post-doctorate.
  2. Create a view that outer joins each of the views created above to the PERSON table.

I have done something similar and it works well.

-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Northrop Grumman    | the other is total destruction. Let us choose wisely.
============================================================================
Received on Mon Jun 20 1994 - 22:16:31 CEST

Original text of this message