outer join in where clause

Hi all,

I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'

The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)

=*, a.property(+) doesn't work

Thanx
LambikI think you just have to add an AND to your ON clause. Try this (not tested, but recollected vaguely from something I tried once before):

select *
from table1 a right outer join table2 b on
a.code = b.code and
a.property = 'TEST'

Looking at it carefully, I think you might have meant to put LEFT OUTER JOIN, but I'm not positive. I always get confused with the Oracle notation. In any case, it should be a simple fix.

Regards,

hmscott

Originally posted by Lambik
Hi all,

I'v a Oracle query which I want to copy in SQL Server.
the query in Oracle is as follows
select *
from table1 a
, table2 b
where a.code(+) = b.code
and a.property(+)='TEST'

The query in SQL Server
select *
from table1 right outer join table2 on table1.code = table2.code
where a.property = 'TEST' (here I don't know what to put)

=*, a.property(+) doesn't work

Thanx
Lambik|||In the original query,
I doubt the "(+)" in "a.property(+)='TEST'" is necessary or even semantically correct? If you remove the it from the query and the
results are still correct, then you can rewrite the query in SQL Server as

Select *
from table1 a, table2 b
where a.code =* b.code
and a.property ='TEST'|||Hi there!

The notation "(+)" defines the outer join in Oracle. So, if you want to have the same results in Oracle and SQL Server you have to convert the "(+)" to an outer join.

Greetings,
Carsten|||where a.code(+) = b.code
and a.property(+)='TEST'

I think what Shianmiin meant was that the both of the (+) notations in this WHERE clause refer to the same table. The first will automatically bring back all non-matching rows on table a making the second one redundant.