Forum: Ruby on Rails Cannot :order when using :offset and :limit in find

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.
Jeff J. (Guest)
on 2006-01-25 18:09
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
Jonathan V. (Guest)
on 2006-01-25 21:43
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 J. 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
>
Jeff J. (Guest)
on 2006-01-26 12:01
Jonathan V. 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 J. 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
KUBO Takehiro (Guest)
on 2006-01-26 18:33
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.
Jeff J. (Guest)
on 2006-01-26 18:42
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
This topic is locked and can not be replied to.