Outer join on multi where clause tables

OK, so my title is confusing...

Anyway, I am trying to blend in several views into a single view. Before, the views broke down kind of like this:

View1

Select p.plat_id,
a.thisandthat,
a.somethingorother,
b.thisandthat,
b.somethingorother
from
platform p,
otherview1 a,
otherview2 b
where
p.plat_id = a.plat_id(+)
and p.plat_id = b.plat_id(+)

the "otherviews" went like:

select a.plat_id,
a.thisandthat,
b.somethingorother
from table1 a,
table2 b
where a.org_id=b.org_id
and a.code="only thing different for both views";


And life was good... however, the REAL view I am blending involved 12 views, and this is causing a distribution nightmare. Therefore, I was just going to make everything into one big query. I can get it to parse fine, but the problem I am having is with foreign keys, since the "otherviews" involved multiple tables.

This is what I have right now:

select p.plat_id,
a1.thisandthat a1_thisandthat,
b1.somethingorother b1_somethingorother,
a2.thisandthat a2_thisandthat,
b2.somethingorother b2_somethingorother
from
platform p,
table1 a1,
table2 b1,
table1 a2,
table2 b2
where
p.plat_id = a1.plat_id(+)
and a1.org_id = b1.org_id
and a1.code="only thing different for both views"
and p.plat_id = a2.plat_id(+)
and a2.org_id = b2.org_id
and a2.code="only thing different for both views";

The data comes out fine except for the outer joins. Wherever there is missing data (remember this is done over 12 different tables), it will discard the whole record. Even though I have an outer join indicator on the main linkage, it still throws out the record.

Does anyone have any idea how I can resolve this?

You need much more (+). Try 2 variants (I'm not sure which is correct)

1)
select p.plat_id,
a1.thisandthat a1_thisandthat,
b1.somethingorother b1_somethingorother,
a2.thisandthat a2_thisandthat,
b2.somethingorother b2_somethingorother
from platform p,
table1 a1,
table2 b1,
table1 a2,
table2 b2
where p.plat_id = a1.plat_id(+)
and a1.org_id = b1.org_id(+)
and a1.code(+) ="only thing different for both views"
and p.plat_id = a2.plat_id(+)
and a2.org_id = b2.org_id(+)
and a2.code(+)="only thing different for both views";

2)
select p.plat_id,
a1.thisandthat a1_thisandthat,
b1.somethingorother b1_somethingorother,
a2.thisandthat a2_thisandthat,
b2.somethingorother b2_somethingorother
from platform p,
table1 a1,
table2 b1,
table1 a2,
table2 b2
where p.plat_id = a1.plat_id(+)
and a1.org_id(+) = b1.org_id
and a1.code(+) ="only thing different for both views"
and p.plat_id = a2.plat_id(+)
and a2.org_id(+) = b2.org_id
and a2.code(+)="only thing different for both views";

The first one :)

- Chris

Thanks for the reply guys, that seems to be working for the most part.

Next question though, some of the views use an IN clause for the a1.code, and I can't use an outer join for an IN or OR clause.

Any ways around that?

Huh? :)

Nevermind, I don't think it matters that I use the IN clause, but to clarify anyway... I was able to use an outer join when the a1.code used an explicit comparison (=), but when I use an IN clause (a1.code IN ('this', 'or', 'that')), then I can't use the outer join.

Ahhh...


Well, for completeness sake, I can show you how to do that anyway.

If you ever have an issue where you can't *directly* use an outer join, you must 'wrap' that inside an in-line view. To wit:

select p.plat_id,
a1.thisandthat a1_thisandthat,
b1.somethingorother b1_somethingorother,
a2.thisandthat a2_thisandthat,
b2.somethingorother b2_somethingorother
from platform p,
(select thisandthat, org_idm plat_id from table1 where code in (a,b,c,d)) a1
table2 b1,
(select thisandthat, org_idm plat_id from table1 where code in (a,b,c,d)) a2
table2 b2
where p.plat_id = a1.plat_id(+)
and a1.org_id = b1.org_id(+)
and p.plat_id = a2.plat_id(+)
and a2.org_id = b2.org_id(+)

Of course, as soon as you do this, you change the plan significantly, so you will have that to worry about, but this is the basic solution

- Chris