Forum: Ruby on Rails Oracle 8i find with :include broken because of LEFT OUTER JO

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ian Z. (Guest)
on 2007-02-02 15:46
(Received via mailing list)
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.
Berger, Daniel (Guest)
on 2007-02-02 18:10
(Received via mailing list)
> 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.
Ian Z. (Guest)
on 2007-02-07 00:23
(Received via mailing list)
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.
This topic is locked and can not be replied to.