Ian Zabel
2007-Feb-02 13:46 UTC
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 -~----------~----~----~----~------~----~------~--~---
Berger, Daniel
2007-Feb-02 16:09 UTC
Re: Oracle 8i find with :include broken because of LEFT OUTERJOIN syntax
> -----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 -~----------~----~----~----~------~----~------~--~---
Ian Zabel
2007-Feb-06 22:22 UTC
Re: Oracle 8i find with :include broken because of LEFT OUTERJOIN syntax
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 -~----------~----~----~----~------~----~------~--~---