Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join is not allowed in operand OR or IN
Outer Join is not allowed in operand OR or IN [message #256380] Sat, 04 August 2007 02:18 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I have difficuly to retrieve the data which used the outer join. Below is the simple example:

LeftTable

Id FirstName LastName  UserName 
 1  Fred     Flinstone freddo 
 2  Homer    Simpson   homey 
 3  Homer    Brown     notsofamous 
 4  Ozzy     Ozzbourne sabbath 
 5  Homer    Gain     noplacelike 

RightTable

IndividualId    AccessLevel 
      1        Administrator 
      2        Contributor 
      3        Contributor 
      4        Contributor 
      10       Administrator 

Result
IndividualId FirstName LastName UserName     IndividualId AccessLevel 
     1         Fred    Flinstone  freddo         1 Administrator 
     2         Homer    Simpson   homey          2   Contributor 
     3         Homer    Brown     notsofamous    3   Contributor 
     4         Ozzy     Osbourne  sabbath        4   Contributor 
    NULL        NULL     NULL      NULL         10 Administrator 



I failed to get the record, explain as below:

From the outer result, I would like to further filter the record where the IndividualId IS NULL or 2.
I have tried to compute my SQL with " WHERE IndividualId IS NULL or IndividualId = '2'. But get error with message "Outer Join is not allowed in operand OR or IN".
Pls advise...........




Re: Outer Join is not allowed in operand OR or IN [message #256382 is a reply to message #256380] Sat, 04 August 2007 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the query.

Regards
Michel
Re: Outer Join is not allowed in operand OR or IN [message #256388 is a reply to message #256382] Sat, 04 August 2007 03:18 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Below is the statement:
select lefttable.username, righttable.accesslevel from
lefttable, righttable where
lefttable.IndividualId = righttable.IndividualId (+)
and righttable.username is null or righttable.username  = 2


pls advise....
Re: Outer Join is not allowed in operand OR or IN [message #256390 is a reply to message #256388] Sat, 04 August 2007 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you test if username is 2?

Regards
Michel
Re: Outer Join is not allowed in operand OR or IN [message #256401 is a reply to message #256390] Sat, 04 August 2007 05:43 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Yup....we are using oracle 8i db, and the error mesg that I get is :

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

pls advise........
Re: Outer Join is not allowed in operand OR or IN [message #256407 is a reply to message #256401] Sat, 04 August 2007 07:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
replace this line
and righttable.username is null or righttable.username  = 2
with
and (righttable.username is null or righttable.username  = 2)



Re: Outer Join is not allowed in operand OR or IN [message #256411 is a reply to message #256407] Sat, 04 August 2007 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rajaram Subramanian, I don't think putting the condition will change anything.
Oracle is right, you can't use outer join with IN or OR.

Ying, you can remove "username=2" as I don't think anybody is called 2. Problem is solved. QED.

Regards
Michel
Re: Outer Join is not allowed in operand OR or IN [message #256492 is a reply to message #256411] Sun, 05 August 2007 01:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

Sorry i didn't see the expected output. For what the OP wants, the outer join should be swapped.

Something like this

Yung,
Your query should be something like this.
select Id, FirstName, LastName, UserName, Individualid, AccessLevel  from
lefttable, righttable where
righttable.IndividualId = lefttable.Id (+) 

Having said that I have worked out a dummy example for usage of a or clause in outer join. Let me know what you think.
SQL> 

create table test (sno number, name varchar2(255));

create table test_child (sno number, child_name varchar2(255), logical_deleted varchar2(1));

Insert into test values (1,'ROW 1');

insert into test values (2,'ROW 2');

insert into test values (3,'ROW 3');

insert into test values (4,'ROW 4');

insert into test_child values (1,'CHILD ROW 1','N');

insert into test_child values (2,'CHILD ROW 2','N');

insert into test_child values (3,'CHILD ROW 3','Y');

commit;

SQL> select * from test;
    SNO NAME
------- -----------------
         1 ROW 1
         2 ROW 2
         3 ROW 3
         4 ROW 4

SQL> select * from test_child;

       SNO CHILD_NAME                L
---------- ------------------------- -
         1 CHILD ROW 1               N
         2 CHILD ROW 2               N
         3 CHILD ROW 3               Y

Say i want to display all the records from test and outer join it with test_child on sno, but where there is a match
i want to display the records where the logical_deleted = 'N'

SQL> select test.sno, test.name, test_child.child_name from test, test_child
where
test.sno = test_child.sno (+)
and
(test_child.logical_deleted is null or test_child.logical_deleted = 'N');

       SNO NAME                      CHILD_NAME
---------- ------------------------- -------------------------
         1 ROW 1                     CHILD ROW 1
         2 ROW 2                     CHILD ROW 2
         4 ROW 4




Your Comments ?

P.S : Code edited to add more comments

[Updated on: Sun, 05 August 2007 02:02]

Report message to a moderator

Re: Outer Join is not allowed in operand OR or IN [message #256493 is a reply to message #256492] Sun, 05 August 2007 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No comment.
Is this what you want?
If so, good.
What comment can I make?

Regards
Michel
Re: Outer Join is not allowed in operand OR or IN [message #256838 is a reply to message #256380] Mon, 06 August 2007 21:00 Go to previous message
ying
Messages: 143
Registered: May 2002
Senior Member
Dear All,

Thanks so much for all the effort to try to provide me the solution.

Yup, this is exactly what I want, special thanks to S.Rajaram..............

I'm really very very very happy.....................



Previous Topic: Calling parametarized procedure in unix shell scripts
Next Topic: user created function taking more time
Goto Forum:
  


Current Time: Sat Dec 10 20:48:40 CST 2016

Total time taken to generate the page: 0.09668 seconds