Re: problem with outer join in a view

Hi.

 I'm not sure it's possible to make a join by NULL value,  but you always can use PL/SQL function instead of join.  ( A bit more expensive, though ).

 HTH. Michael.

In article <8df7o1$o35$1_at_nnrp1.deja.com>,   andreas.rezmann_at_cas.de wrote:
> Hello,
> I have a problem with a view containing outer
> joins:
>
> The following tables exist in the database:
>
> create table A
> (
> AGGUID RAW(16) not null,
> A1 VARCHAR2(10) not null,
> A2 VARCHAR2(10) null ,
> A3 VARCHAR2(10) null ,
> A4 VARCHAR2(10) null ,
> constraint PK_A primary key (AGGUID)
> )
> /
> create table B
> (
> BGGUID RAW(16) not null,
> B1 VARCHAR2(10) not null,
> B2 VARCHAR2(10) null ,
> B3 VARCHAR2(10) null ,
> B4 VARCHAR2(10) null ,
> constraint PK_B primary key (BGGUID)
> )
> /
>
> Table A contains the following records
>
> A1 A2 A3...
> --------------------------------------
> AA1 null ...
> AA2 null ...
> AA2 ZK5 ...
> AA3 ZK7 ...
> AA4 null ...
>
> Table B contains the following records
>
> B1 B2 B3...
> --------------------------------------
> AA1 ZK6 ...
> AA3 ZK7 ...
> AA4 null ...
>
> The problem is to create a view with the
> following result:
> All records from table A (->Outer Join)
> and related records from table B
> (Related means A1=B1 and A2=B2)
>
> The result should look like
>
> A1 A2 B1 B2 ...
> --------------------------------------
> AA1 null (no record from table B)
> AA2 null (no record from table B)
> AA2 ZK5 (no record from table B)
> AA3 ZK7 AA3 ZK7 ...
> AA4 null AA4 null ...
>
> The view
> create or replace view VIEW1 as
> select a.*, b.*
> from a, b
> where ( a.a1 = b.b1(+) )
> and ( a.a2 = b.b2(+) )
> with check option
> /
> generates
> the following result
> A1 A2 B1 B2 ...
> --------------------------------------
> AA1 null (no record from table B)
> AA2 null (no record from table B)
> AA2 ZK5 (no record from table B)
> AA3 ZK7 AA3 ZK7 ...
> AA4 null (no record from table B)
>
> That means that the related record from table B
> with a NULL in the field B2 was not found.
>
> Next try:
>
> The view
> create or replace view VIEW1 as
> select a.*, b.*
> from a, b
> where ( a.a1 = b.b1(+) )
> and ( ( a.a2 = b.b2 ) or
> ( a.a2 is null and b.b2 is null ) )
> with check option
> /
> generates
> the following result
> A1 A2 B1 B2 ...
> --------------------------------------
> AA2 null ...
> AA3 ZK7 AA3 ZK7 ...
> AA4 null AA4 null ...
>
> That means that records with corresponding fields
> are found but the records from table A
> without corresponding records are not shown.
>
> How can a solution look like?
>
> Thank you in advance
>
> Andreas Rézmann
> CAS Software AG
> Germany
> andreas.rezmann_at_cas.de
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 17 2000 - 00:00:00 CDT