Hi there. I have a problem with an oql statement that uses orderby, offset and limit
The objects im trying to load (company) have a collection (workers)
if i oql them normally i get the full resultset back. If i use the order by,
ofset, limit it only gives my part of what i should be getting
for example:
oql = db.getOQLQuery("SELECT e FROM com.bean.company e WHERE
is_undefined(e.date) ORDER BY e.companyid LIMIT $1 OFFSET $2");
oql.bind(10);
oql.bind(1);
this oql will return me like 3 rows. Depending on the ofset this changes(the
number of objects returned)
If i take out the collection from the mapping it returns me the correct ones.
any ideas??
here is the query returned by the results object for the same query with and
without the collection
"select * from ( SELECT
\"EMPRESA\".\"IDEMPRESA\",\"EMPRESA\".\"NIFEMPRESA\",\"EMPRESA\".\"NOMEMPRESA\",\"EMPRESA\".\"IMPORTANDO\",\"EMPRESA\".\"NUMUSUARIOS\",\"EMPRESA\".\"DATAMODIFICACION\",\"EMPRESA\".\"DATACREACION\",\"EMPRESA\".\"DATAELIMINACION\",\"EMPRESA\".\"ULTIMOUSUARIO\",\"INFORME\".\"IDINFORME\"
, rank() over ( ORDER BY \"EMPRESA\".\"IDEMPRESA\" ) rnk FROM
\"INFORME\",\"EMPRESA\" WHERE
\"EMPRESA\".\"IDEMPRESA\"=\"INFORME\".\"ID_EMPRESA\"(+) AND
(\"EMPRESA\".\"DATAELIMINACION\" IS NULL ) ) where rnk - ?2 between 1 and ?1 "
"select * from ( SELECT
\"EMPRESA\".\"IDEMPRESA\",\"EMPRESA\".\"NIFEMPRESA\",\"EMPRESA\".\"NOMEMPRESA\",\"EMPRESA\".\"IMPORTANDO\",\"EMPRESA\".\"NUMUSUARIOS\",\"EMPRESA\".\"DATAMODIFICACION\",\"EMPRESA\".\"DATACREACION\",\"EMPRESA\".\"DATAELIMINACION\",\"EMPRESA\".\"ULTIMOUSUARIO\"
, rank() over ( ORDER BY \"EMPRESA\".\"IDEMPRESA\" ) rnk FROM \"EMPRESA\"
WHERE (\"EMPRESA\".\"DATAELIMINACION\" IS NULL ) ) where rnk - ?2 between 1 and
?1 "
It seems that the problem is with objets that have more than one item in the
collection. It counts them without distinct so out of the 10 objects i should
get, i get only five because one of them has five items in the collection.
I have tried using distinct but no dice.
any ideas. Is this a known issue or do you think its my programming?
Stefanos Kollias
Dptos. Enxenería do Software e Web
__________________________________________________________
Imaxin|Software
Rúa Salgueiriños de Abaixo, nº 11 - Local 6
15891 Santiago de Compostela A Coruña Gz Sp
Voz +34 981 554 068 Fax +34 981 554 988
E-mail [EMAIL PROTECTED]
__________________________________________________________
Web www.imaxin.com
smime.p7s
Description: S/MIME cryptographic signature
