Re: problem with outer join in a view

Remeber, indexes don't store NULL values anyway, so if he needs to join on a NULL condition, he can't use the index anyway...

Michael J. Ort

For the sake of those who might be reading this thread to solve a similar problem, I am included the text of an e-mail from the original poster to clarify a syntax error in my suggestion (I'm only human):

Thanks, this is the solution, but the (+) has to be close to the fieldname:

...=nvl(b.b2(+),'NULL')

Regards
Andreas

In article <8diejg$bnl$1_at_nnrp1.deja.com>,   michael_bialik_at_my-deja.com wrote:
> 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.
>

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