Oracle vs Sql Server (LEFT OUTER JOIN)
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