We are having an issue while trying to run queries that use a fetch limit in
Oracle. The cayenne adapter seems to generate a SQL query something like
select *
from CONTENTTEXTPUB
where contentid in (select recordid from content where
CONTENTCHANNELID = '8010149aafb30fe21075891007e80')
and rownum<=5
order by INDEXMASTERIDENTIFIERS
Which does not return the correct number of rows. In this case it returns 2
rows instead of 4. By embedding the rownum inside the main query it looks
like Oracle applies it before filtering the data.
If we change the query to look something like:
select * from
( select *
from CONTENTTEXTPUB
where contentid in (select recordid from content where
CONTENTCHANNELID = '8010149aafb30fe21075891007e80')
order by INDEXMASTERIDENTIFIERS
)
where rownum <= 5 ;
Then the correct number of rows is returned. Obviously this is Oracle
specific only.
Question: How can we change the way the fetchlimit is implemented in Cayenne
for Oracle only? Cayenne SQL server seems to apply the fetch limit during
the result set traversal. Our goal would be to use that same mechanism for
Oracle.
-- Dov Rosenberg Inquira Inc 370 Centerpointe Circle, ste 1178 Altamonte Springs, FL 32701 (407) 339-1177 x 102 (407) 339-6704 (fax) drosenber..nquira.com AOL IM: dovrosenberg
This archive was generated by hypermail 2.0.0 : Wed Feb 15 2006 - 15:22:00 EST