Rails - Jan 2006 - Help constructing a find_by_sql command

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

-- 
Posted via http://www.ruby-forum.com/.

Jeff Jones 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


-- 
Posted via http://www.ruby-forum.com/.