Query to be run against both SQL and Oracle

NK · August 24th, 2003, 01:16 AM

Hi
I've been made aware that in Oracle 8 db, outer and inner joins donot work.
How can use the foll query to work against both dbs.

SELECT AppUser.Forename, AppUser.Surname, AppUser.ID
FROM Training_Course_AppUser LEFT OUTER JOIN
AppUser_TrainingCourse ON Training_Course_AppUser.AppUser_ID = AppUser_TrainingCourse.AppUser_ID AND
Training_Course_AppUser.Course_ID = AppUser_TrainingCourse.Course_ID RIGHT OUTER JOIN
AppUser ON Training_Course_AppUser.AppUser_ID = AppUser.ID
WHERE Training_Course_AppUser.Course_ID = 125 And AppUser_TrainingCourse.Completion_Date Is Null

regards
N

jlnashrod · April 22nd, 2004, 03:34 AM

why don't you try (+) or union syntax to combine both tables

Justin Cave · April 26th, 2004, 05:28 PM

If you're doing an outer join, the problem is that Oracle doesn't support the SQL 99 syntax until 9i and SQL Server doesn't support the Oracle join operator (+).

What API are you using to execute the query? The ODBC API defines an escape sequence for outer joins that the individual ODBC drivers will convert to the appropriate native join syntax.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC