Forum: Ruby on Rails Help constructing a find_by_sql command

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-26 17:54
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
Jeff J. (Guest)
on 2006-01-26 18:43
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 :)

Move along, nothing to see here.

Jeff
This topic is locked and can not be replied to.