Re: outer join and OR

From: Chrysalis <cellis_at_iol.ie>
Date: 1998/02/26
Message-ID: <34F51E27.3D2@iol.ie>

SQL> Rem List tables
SQL> select * from customers;

  col1 INT1      
INT2                                                                          


------ ----------

----------                                                                    

     1 abc       
xxx                                                                           
     2 abc       
abc                                                                           
     3 xxx       
abc                                                                           
     4 xxx       
xxx

4 rows selected.

SQL> select * from notes;

col1
TXT

6 rows selected.

SQL> Rem
SQL> Rem This works! (Your example)
SQL> Rem
SQL> select C.col1, C.int1, C.int2, N.txt

  2 from customers C, notes N
  3 where N.col1(+) = C.col1
  4 and (C.int1 = 'abc' or C.int2 = 'abc');

  col1 INT1       INT2      
TXT                                                                


------ ---------- ----------

----------                                                         

     1 abc        xxx       
1a                                                                 
     1 abc        xxx       
1b                                                                 
     2 abc       
abc                                                                           
     3 xxx        abc       
3a                                                                 
     3 xxx        abc       
3c

5 rows selected.

SQL> 
SQL> Rem
SQL> Rem If the OR is on the outer-joined table
SQL> Rem we get the error you referred to.
SQL> Rem Is this what you actually want to do?
SQL> Rem
SQL> select C.col1, C.int1, C.int2, N.txt

  2 from customers C, notes N
  3 where N.col1(+) = C.col1
  4 and (N.txt(+) like '%a' or N.txt(+) like '%b'); and (N.txt(+) like '%a' or N.txt(+) like '%b')

ERROR at line 4:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

SQL> 
SQL> Rem
SQL> Rem This doesn't work either!
SQL> Rem
SQL> select C.col1, C.int1, C.int2, N.txt

  2 from customers C, notes N
  3 where N.col1 (+) = C.col1
  4 and substr(N.txt(+),2,1) in ('a','b'); and substr(N.txt(+),2,1) in ('a','b')

ERROR at line 4:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

SQL> 
SQL> Rem
SQL> Rem This does work for some reason! 
SQL> Rem Goodness know why, but it 
SQL> Rem probably isn't very helpful!
SQL> Rem
SQL> select C.col1, C.int1, C.int2, N.txt

  2 from customers C, notes N
  3 where N.col1 (+) = C.col1
  4 and substr(N.txt(+),2,1) NOT in ('c','d');

  col1 INT1       INT2      
TXT                                                                


------ ---------- ----------

----------                                                         

     1 abc        xxx       
1a                                                                 
     1 abc        xxx       
1b                                                                 
     2 abc       
abc                                                                           
     3 xxx        abc       
3a                                                                 
     4 xxx       
xxx

5 rows selected.

SQL> 
SQL> Rem
SQL> Rem If this is what you really want to do,
SQL> Rem you may find an in-line view useful.
SQL> Rem
SQL> select C.col1, C.int1, C.int2, N.txt
  2  from   customers C
  3       ,(select col1,txt from notes
  4         where (txt like '%a' or txt like '%b')
  5        ) N

6 where N.col1 (+) = C.col1;

  col1 INT1       INT2      
TXT                                                                


------ ---------- ----------

----------                                                         

     1 abc        xxx       
1a                                                                 
     1 abc        xxx       
1b                                                                 
     2 abc       
abc                                                                           
     3 xxx        abc       
3a                                                                 
     4 xxx       
xxx

5 rows selected.

SQL>
SQL> spool off;

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards, Guards"

Lily Djuniarti wrote:
> 
> Let's say the two tables I'm trying to join are: Customer (eg. columns:
> col1, int1 and int2) and Notes. A customer may or may not have notes, so to
> pull all customers (with or without notes) I used outer join.
> 
> select customer.col1, customer.int1, customer.int2, notes.col1
> from customer, notes
> where customer.col1 = notes.col1(+)
> and (customer.int1 = 'abc' OR customer.int2 = 'abc');
> 
> Since I cannot use an OR in an outer join, this doesn't seem to work.
> How can I solve this?
> 
> Tx,
> LD
> 
> Chrysalis <cellis_at_iol.ie> wrote in article <34F4DEEA.4BD0_at_iol.ie>...
> > Lily Djuniarti wrote:
> > >
> > > I need to perform an outer join and also use an OR operator, which I learn
> > > is not possible. Is there any way out ?
> > >
> > > Thanks,
> > > LD
> >
> > I don't know.
> > What are you trying to do?
> > --
> > Chrysalis
> >
> > FABRICATI DIEM, PVNC
> > ('To Protect and to Serve')
> > Terry Pratchett : "Guards, Guards"
> >

Received on Thu Feb 26 1998 - 00:00:00 CST