problem with outer join in a view

From: <andreas.rezmann_at_cas.de>
Date: 2000/04/17
Message-ID: <8df7o1$o35$1@nnrp1.deja.com>#1/1

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. Received on Mon Apr 17 2000 - 00:00:00 CDT