Cannot :order when using :offset and :limit in find

Rails
1

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

2

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

3

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 :wink:

Jeff

4

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.

5

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 :slight_smile:

Jeff