Rails - Feb 2007 - Oracle 8i find with :include broken because of LEFT OUTER JOIN syntax

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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> -----Original Message-----
> From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org 
> [mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org] On Behalf
Of Ian Zabel
> Sent: Friday, February 02, 2007 6:46 AM
> To: Ruby on Rails: Talk
> Subject: [Rails] Oracle 8i find with :include broken because 
> of LEFT OUTERJOIN syntax
> 
> 
> 
> 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?
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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

On Feb 2, 11:09 am, "Berger, Daniel"
<Daniel.Ber...-6vC2hAQLr78AvxtiuMwx3w@public.gmane.org>
wrote:
> > -----Original Message-----
> > From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> > [mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org] On
Behalf Of Ian Zabel
> > Sent: Friday, February 02, 2007 6:46 AM
> > To: Ruby on Rails: Talk
> > Subject: [Rails]Oracle8i find with :include broken because
> > of LEFT OUTERJOIN syntax
>
> > InOracle8i, the syntax "LEFTOUTERJOIN" is not supported.
> > It is supported inOracle9i.
>
> > 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
> >OUTERJOINproject ON project.projectno = work.projectno
> > LEFTOUTERJOINfunction ON function.functionno > > work.functionno
LEFTOUTERJOINaccount ON account.accountno
> > = work.accountno WHERE (developerid > > ''iwz'')
>
> > If you take a look at the query, it is using syntax not
> > supported byOracle8i. SELECT count(DISTINCT work.workno) AS
> > count_all FROM work LEFTOUTERJOINproject ON
> > project.projectno = work.projectno LEFTOUTERJOINfunction
> > ON function.functionno = work.functionno LEFTOUTERJOIN
> > 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?
>
> 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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---