Hi All,
I am facing a problem with Oracle / Sql Server owing to the way they treat left outer joins.
Here is an example.
[CODE]
For SQL Server
CREATE TABLE LEFTTAB(A VARCHAR(1), B VARCHAR(10), C varchar(10))

CREATE TABLE RIGHTTAB(A VARCHAR(1), B VARCHAR(10), C varchar(10))

insert into lefTtab values('A','Example','Sample')

select A.A, B.A from lefttab A left outer join righttab B
ON A.A = B.A AND A.C = B.C AND A.B = B.B AND b.b = 'IND'
'The select query will always return a row whether table b contains any rows or not.
[CODE]

Code:
'For Oracle
CREATE TABLE LEFTTAB(A VARCHAR2(1), B VARCHAR2(10), C varchar2(10))

CREATE TABLE RIGHTTAB(A VARCHAR2(1), B VARCHAR2(10), C varchar2(10))

insert into lefTtab values('A','Example','Sample')

select A.A, B.A from lefttab A, righttab B 
	WHERE A.A = B.A(+) AND A.C = B.C(+) AND A.B = B.B(+) AND b.b = 'IND'
'will not a return any rows unless I drop out the condition b.b = 'IND'
If I change the query to this

select A.A, B.A from lefttab A, righttab B 
	WHERE A.A = B.A(+) AND A.C = B.C(+) AND A.B = B.B(+) OR b.b = 'IND'

I get the following error message

ERROR at line 2:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
What should I do?

Perplexingly,
Abhijit