Oracle 8i find with :include broken because of LEFT OUTER JO

Rails
1

In Oracle 8i, the syntax “LEFT OUTER JOIN” is not supported. It is
supported in Oracle 9i.

If I try to find some records using something like this on model
called Work, I get an error:
:include => [:project, :function, :account]

The error is:
OCIError: ORA-00933: SQL command not properly ended: SELECT
count(DISTINCT work.workno) AS count_all FROM work LEFT OUTER JOIN
project ON project.projectno = work.projectno LEFT OUTER JOIN
function ON function.functionno = work.functionno LEFT OUTER JOIN
account ON account.accountno = work.accountno WHERE (developerid =
‘iwz’)

If you take a look at the query, it is using syntax not supported by
Oracle 8i.
SELECT count(DISTINCT work.workno) AS count_all
FROM work
LEFT OUTER JOIN project ON project.projectno = work.projectno
LEFT OUTER JOIN function ON function.functionno = work.functionno
LEFT OUTER JOIN account ON account.accountno = work.accountno
WHERE (developerid = ‘iwz’)

Here is how the SQL should be formatted:

SELECT count(DISTINCT work.workno) AS count_all
FROM work, project, function, account
WHERE (developerid = ‘iwz’) and
project.projectno(+) = work.projectno and
function.functionno(+) = work.functionno and
account.accountno(+) = work.accountno

Does anyone know of a workaround, patch, or anything regarding this?

Thanks!
Ian.

2

In Oracle 8i, the syntax “LEFT OUTER JOIN” is not supported.
LEFT OUTER JOIN function ON function.functionno =
account ON account.accountno = work.accountno WHERE
account.accountno(+) = work.accountno

Does anyone know of a workaround, patch, or anything regarding this?

Project.find_by_sql, I’m afraid.

Unless there’s a way to alter the sql generation method that I don’t
know about. If not, I would consider this a bug.

Regards,

Dan

This communication is the property of Qwest and may contain confidential
or
privileged information. Unauthorized use of this communication is
strictly
prohibited and may be unlawful. If you have received this communication
in error, please immediately notify the sender by reply e-mail and
destroy
all copies of the communication and any attachments.

3

Is
ActiveRecord::Associations::ClassMethods::JoinDependency::JoinAssociation
the correct place to look at to potentially fix this issue?

Or would it be in some Oracle specific part of Rails? Certainly, not
every database supported by rails supports the “LEFT OUTER JOIN”
syntax? Or, do they all?

Ian.