Re: Migrating an Application from SQL Server 2000 to Oracle 10g

> > SELECT a.a, a.b, b.a, b.b from Database1.dbo.Table1 a LEFT JOIN
> > Database2.dbo.Table1 b WHERE a.c = '123'

I'm quite sure this query doesn't work on any db (at least definitely not on SQL Server and Oracle) because it lacks join condition (ON clause)

>
> The ansi sql works in Oracle,
> I think (I don't use it).

The so called ANSI style definitely works in Oracle. So no need to change them somehow, except of course that either database1 and database2 or dbo should be removed. Spekaing of joins I've tried to classify them and also compare what works in Oracle and SQL server and what not here:
http://www.gplivna.eu/papers/sql_join_types.htm

> select d1.a, d1.b, d2.a, d2.b
> from dbo1 d1, dbo2 d2
> where d1.c=d2.c
> and d1.c='123'
> /

If the original query would have join condition and therefore would be valid query, then in the old oracle way we had to use outer join operator (+) because LEFT indicates it is outer join. But as of today there is absolutely no need to do that especially for already working code.

Gints Plivna
http://www.gplivna.eu Received on Wed Jul 16 2008 - 11:30:02 CDT