hi,
the case is this:
- i execute a QueryByCriteria using the IteratorByQuery method
- i get an RsIterator back
- i call the size method of this object
Now this happens:
- the RsIterator has to execute a count query to get the size
- the iterator first has to create a count query and for this it uses the
stored queryobject
This is were/how/why it goes wrong:
- the original query had an orderby, this order by is not removed for the
count query,
so the field/column is also included in the columnlist of values to
retrieve, next to the
count() function.
this in itself is wrong because now the query will return get multiple
rows all with incorrect
count values (not the accumalated sum).
this is also wrong because some databases require a groupby clause when
mixing
non-groupby columns and groupby columns (count()), as example the MySQL
database.
So i get an error because of wrong sql syntax.
Building a count query, the ordering should be removed!
The generated SQL was:
SELECT count(distinct A0.ID),A0.DESCRIPTION as ojb_col_1
FROM IBANX_PICKLIST_ITEM A0
WHERE A0.PICKLIST_NAME = 'classification'
ORDER BY 2
It should be:
SELECT count(distinct A0.ID
FROM IBANX_PICKLIST_ITEM A0
WHERE A0.PICKLIST_NAME = 'classification'
An extra note is that this syntactically correct query is still wrong for
calculating the size!
In calculates the fullsize, so in effect this method on the RsIterator would
return the wrong value.
greetings,
Roger Janssen
iBanx
*************************************************************************
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed.You should not copy, disclose or distribute this communication
without the authority of iBanx bv. iBanx bv is neither liable for
the proper and complete transmission of the information has been maintained
nor that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.
