Jeff Jones
2006-Jan-25 16:10 UTC
[Rails] Cannot :order when using :offset and :limit in find
Hello all. I am using the Paginate_with_ajax code as described on the wiki but I am running into the following problem. The following code will work fine and retrieved unsorted records in a hunky dory fashion: @componentlogs = Componentlog.find(:all, :conditions => [ "cl_compname like ?", @criteria ], :offset => offset, :limit => items_per_page ) However if I try and add an order... @componentlogs = Componentlog.find(:all, :conditions => [ "cl_compname like ?", @criteria ], :offset => offset, :limit => items_per_page, :order => "cl_spr DESC" ) It goes totally doolally. OCIError: ORA-00907: missing right parenthesis: select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM componentlog WHERE (cl_compname like ''CAPODIR'') ORDER BY cl_spr DESC ) raw_sql_ where rownum <= 25) where raw_rnum_ > 0 I don''t have much control over this (line is there, line isn''t, line is there, line isn''t...) and would rather not resort to using a find_by_sql but if I have to I will. I am running on oracle using the oci driver Any help would be much appreciated! Thanks Jeff -- Posted via http://www.ruby-forum.com/.
Jonathan Viney
2006-Jan-25 19:43 UTC
[Rails] Re: Cannot :order when using :offset and :limit in find
My guess is it is a problem with the Oracle adapter. I was able to use all those options together on MySQL. But, then again, that''s probably not much help because the syntax in MySQL is different :). -Jonny. Jeff Jones wrote:> > It goes totally doolally. > > OCIError: ORA-00907: missing right parenthesis: select * from (select > raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM componentlog WHERE > (cl_compname like ''CAPODIR'') ORDER BY cl_spr DESC ) raw_sql_ where > rownum <= 25) where raw_rnum_ > 0 > > I don''t have much control over this (line is there, line isn''t, line is > there, line isn''t...) and would rather not resort to using a find_by_sql > but if I have to I will. > > I am running on oracle using the oci driver >-- Posted via http://www.ruby-forum.com/.
Jeff Jones
2006-Jan-26 10:01 UTC
[Rails] Re: Cannot :order when using :offset and :limit in find
Jonathan Viney wrote:> My guess is it is a problem with the Oracle adapter. I was able to use > all those options together on MySQL. But, then again, that''s probably > not much help because the syntax in MySQL is different :). > > -Jonny. > > Jeff Jones wrote: >> >> It goes totally doolally. >> >> OCIError: ORA-00907: missing right parenthesis: select * from (select >> raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM componentlog WHERE >> (cl_compname like ''CAPODIR'') ORDER BY cl_spr DESC ) raw_sql_ where >> rownum <= 25) where raw_rnum_ > 0 >> >> I don''t have much control over this (line is there, line isn''t, line is >> there, line isn''t...) and would rather not resort to using a find_by_sql >> but if I have to I will. >> >> I am running on oracle using the oci driver >>Nutbunnies, I was afraid of that. Off to bug (har har, I kill myself) the guy who wrote the driver ;) Jeff -- Posted via http://www.ruby-forum.com/.
KUBO Takehiro
2006-Jan-26 16:34 UTC
[Rails] Re: Cannot :order when using :offset and :limit in find
Hello, Jeff Can you run the following SQL on sqlplus? select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM componentlog WHERE (cl_compname like ''CAPODIR'') ORDER BY cl_spr DESC ) raw_sql_ where rownum <= 25) where raw_rnum_ > 0 I guess that your Oracle server version is 8.0, which doesn''t support order-by clause in a subquery. -- Posted via http://www.ruby-forum.com/.
Jeff Jones
2006-Jan-26 16:42 UTC
[Rails] Re: Cannot :order when using :offset and :limit in find
KUBO Takehiro wrote:> Hello, Jeff > > Can you run the following SQL on sqlplus? > > select * from (select > raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM componentlog WHERE > (cl_compname like ''CAPODIR'') ORDER BY cl_spr DESC ) raw_sql_ where > rownum <= 25) where raw_rnum_ > 0 > > I guess that your Oracle server version is 8.0, which doesn''t support > order-by clause in a subquery.Hi Takehiro. Ahhhh right, yes you are correct. Tt doesn''t work in SQL plus either. I was under the impression that it was a problem with the adaptor or the offset / limit bit. Is there a way to mimick that sort of functionality using a hackish approach? Manys thanks for the clarification and I suppose you can delte with extreme prejudice that bug I raised on the rubyforge page :) Jeff -- Posted via http://www.ruby-forum.com/.