Rails - Jan 2006 - 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/.

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/.

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/.

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/.

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/.