Help constructing a find_by_sql command

Rails
1

Hello all. I am trying to do the equivalent of:

@componentlogs = Componentlog.find(:all,
:conditions => [ “cl_compname like ?”, @criteria ],
:offset => offset,
:limit => items_per_page,
:order => “cl_spr DESC” )

in a find_by_sql statement. I cannot use the build in because the
adaptor isn’t quite right (OCI8)

When I use it I get the following error

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 am not sure how to convert this into a find_by_sql statement, I tried:

result = Componentlog.find_by_sql [“select * from (select raw_sql_.*,
rownum raw_rnum_ from (SELECT * FROM componentlog WHERE (cl_compname
like ?) ORDER BY cl_spr DESC ) raw_sql_ where rownum <= ? ) where
raw_rnum_ > ? )”, criteria, limit, offset]

Which mimicks the error SQL with with an extra right paranethesis but I
still get the same error. Can any SQL guru give me a helping hand?

Thanks

Jeff

2

Jeff J. wrote:

Hello all. I am trying to do the equivalent of:

@componentlogs = Componentlog.find(:all,
:conditions => [ “cl_compname like ?”, @criteria ],
:offset => offset,
:limit => items_per_page,
:order => “cl_spr DESC” )

in a find_by_sql statement. I cannot use the build in because the
adaptor isn’t quite right (OCI8)

When I use it I get the following error

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 am not sure how to convert this into a find_by_sql statement, I tried:

result = Componentlog.find_by_sql [“select * from (select raw_sql_.*,
rownum raw_rnum_ from (SELECT * FROM componentlog WHERE (cl_compname
like ?) ORDER BY cl_spr DESC ) raw_sql_ where rownum <= ? ) where
raw_rnum_ > ? )”, criteria, limit, offset]

Which mimicks the error SQL with with an extra right paranethesis but I
still get the same error. Can any SQL guru give me a helping hand?

Thanks

Jeff

Never mind, it was a limitation with the database, not a problem with
the adaptor :slight_smile:

Move along, nothing to see here.

Jeff