Ola Stefanos,
I'll try to take a look at this but could you please tell me the version
of Castor and the database system you are using. Could also post the
relevant part of your mapping?
Ralf
Stefanos schrieb:
> 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] <mailto:[EMAIL PROTECTED]>
> __________________________________________________________
> Web www.imaxin.com <http://www.imaxin.com>
--
Syscon Ingenieurbüro für
Meß- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstraße 11
D-72127 Kusterdingen
Germany
Tel. +49 7071 3690 52
Mobil: +49 173 9630135
Fax +49 7071 3690 98
Email: [EMAIL PROTECTED]
Web: www.syscon-informatics.de
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
