Re: Can I do a conditional join?

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... Received on Tue Jun 14 1994 - 18:01:40 CEST