Re: problem with outer join in a view

Hi.

 In that case the NVL function prevents usage of index ( at least  partial ), so Oracle is going to use RANGE SCAN access even  if the index is unique.
 It still may be good if the selectivity of first index field is good.

 HTH. Michael.

In article <8dfpvc$dg3$1_at_nnrp1.deja.com>,   Michael J. Ort <michael_ort_at_my-deja.com> wrote:
> The reason that 'the related record from table B with a NULL in the
> field B2 was not found' is because NULL is not equal to NULL, thus
 a.a2
> is not equal to b.b2 in this case. Remeber NULL is not not equal to
> NULL either, so you can't use that either.
>
> Try:
>
> create or replace view VIEW1 as
> select a.*, b.*
> from a, b
> where ( a.a1 = b.b1(+) )
> and ( nvl(a.a2,'NULL') = nvl(b.b2,'NULL')(+) )
> with check option
>
> HTH,
> Michael J. Ort
>
> 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.
>

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