Hi,
I'm having difficulties in translating the Oracle syntax into SQLServer.
Actually, I'm trying to translate something like :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1, Tab2
Where Tab1.a = Tab2.c (+)

I tried :

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1
RIGHT OUTER JOIN Tab2 ON Tab1.a = Tab2.c

The results seem similar but it occurs that in Oracle, the "Where Tab1.a = Tab2.c (+)" performs some kind of filter !!!

Can anybody help ?

Counterintuitively, the ANSI equivalent here would be LEFT OUTER JOIN not RIGHT OUTER JOIN.

hello ,

Select Tab1.a, Tab1.b, Tab2.c, Tab2.d
from Tab1,Tab2
where Tab1.a =* Tab2.c

here it will includes all rows for Tab2 and Tab1 is a null suppling table.

hope this will help,
Pooja

Thanks for your answers but err...
I'm sorry but (I'm tired this morning) there's a mistake in my question...

My problem occurs when compared to a constant :

SELECT
TAB1.A,
TAB1.B
FROM
TAB2,
TAB1
WHERE
TAB1.A= TAB2.C (+) AND
TAB2.D(+) = -1

How do you translate "TAB2.D(+) = -1" (which performs some kind of filter I don't understand) ?

Thx

What is does is only try to match TAB1 records to TAB2 records where D = -1.

I think with ANSI syntax you probably have to do the filter before the outer join:

SELECT TAB1.A, TAB1.B
FROM TAB1
LEFT OUTER JOIN (SELECT * FROM TAB2 WHERE d=-1) t2
ON TAB1.a = T2.c;

But unless you actually want to select some columns from TAB2, this outer join is pointless - i.e. the result will be same as:

SELECT TAB1.A, TAB1.B
FROM TAB1;

This is very useful, thank you for your help.

(and yes, I'll select some columns from TAB2)

I found another way to do it, maybe it is more quick to execute...

SELECT TAB1.A, TAB1.B, TAB2.e
FROM TAB1
LEFT OUTER JOIN TAB2 ON TAB1.a = TAB2.c AND TAB2.d=-1;

What do you think ?

Yes, if that works it's probably better than using an inline view. I can't check at the moment (no 9i here), but I had a doubt about doing that for some reason I can't remember.