hi danilo,
i propose to do the following :
1.) select count(*) for QueryByCriteria not using distinct
ok!
2.) select count (distinct key1 || key2) for DISTINCT QueryByCriteria
ok! if you can really make it db independant, you must probably add some sort 'generateConcat' method in the Platform implementations and maybe you need some type conversion on the keys to concatenate them wich is also db dependant :(
3.) special treatment for ReportQueryByCriteria because groupBy is only relevant here.this is true for ex. for oracle, because all selected columns must be also declared in the group by cause, but not all databases require this...
actually i am using it with a normal QueryByCriteria ;) on schei$$base, however i am not an SQL guru and i do not know if it is a standard behaviour what Sybase does with count on a 'group by' query.
(Sybase delivers a relsutset of counts on the number of elements in the groups and not the number of delivered groups)
i can see no super general solution that will work on every database...
maybe the best solution is just to throw an UnsupportedOperationException if the GroupBy is set (or optionally silently ignored by config in OJB.repository for backwards compatibility)
gruss aus z�ri danilo
Danilo Tommasina wrote:
hi Jakob,
a ditinct clause for doing exactly what i need, however scheissbase <=
this isn't the official name, isn't it ;)
no it isn't, but from my point of view it better describes the product ;)
i was thinking of:
select count (distinct key1 || key2) from .... when we have multiple pks.
this does of course not solve the group by at all :( and of course string concatenation is not the same on any platform (|| is the ansi char for concatenation, but ms sql-server uses +, ms-access &, mysql has a function called concat...) how is it in s...base ?
imo an easy way to solve the problems would be to use an enclosing count query:
select count(*) from (original query)
but this does not yet work in mysql !
so, sorry for the long text, but maybe sombody else has the same problem and that's the (horrible) way i solved it.
if somebody has a better solution i would be glad to hear it, thanks
Ok, I see... FYI Sybase also uses || for string concatenation.
However it may require a type conversion for the string-concatenation, that is also rdms dependant.
as you said, this doesn't solve the group by problem, however i do not know if all databases returns the same results with different combinations of group by and distinct clauses. so may be it is not that bad to ignore the group by clause in the getCount() method (however in this case a note in the doc should be done)
so for my needs i will just adopt my special (horrible) [EMAIL PROTECTED] solution.
thx danilo
hi danilo,
the groupBy information is defined in the query not in the criteria, so the copy() does not make much sense. but there's a problem when counting reportqueries using groupBy:
Criteria crit = new Criteria();
ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit);
q.setColumns(new String[] { "groupName", "sum(allArticlesInGroup.stock)", "sum(allArticlesInGroup.price)" });
q.addGroupBy("groupName");
broker.getReportQueryIteratorByQuery(q); while (iter.hasNext()) { results.add(iter.next()); }
int count = broker.getCount(q); assertEquals(results.size(), count); <<< FAILS
results in this sql:
SELECT A0.KategorieName,sum(A1.Lagerbestand),sum(A1.Einzelpreis) FROM Kategorien A0 LEFT OUTER JOIN BOOKS A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr LEFT OUTER JOIN CDS A1E2 ON A0.Kategorie_Nr=A1E2.Kategorie_Nr LEFT OUTER JOIN Artikel A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr GROUP BY A0.KategorieName
ojb generates a wrong count-sql :
SELECT count(*) FROM Kategorien A0
or when including groupBy:
SELECT count(*),A0.KategorieName as ojb_col_1 FROM Kategorien A0 GROUP BY A0.KategorieName
how should the count look like (without using a subquery) ?
jakob
Danilo Tommasina wrote:
Hi,
Is there a reason why the GroupBy clause is beeing ignored in a query when doing a PersistenceBorker.getCount( Query query )?
In RC4 release I found this piece of code:
class PersistenceBokerImpl
public int getCount(Query query) throws PersistenceBrokerException
{
(...)
// build a ReportQuery based on query
// orderby needs to be cleared
if (query.getCriteria() != null)
{
reportCrit = query.getCriteria().copy(false, false, false);
}
(...)
}
The line:
reportCrit = query.getCriteria().copy(false, false, false);
copies the Criteria ignoring the GroupBy clause, thus causing the count query to return a wrong number of elements.
Is there a reason for this or has it been fixed after RC4?
thx bye Danilo
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
-- Danilo Tommasina, Dipl. Ing. FH Telecom Software Engineer RCS Riskmanagement Concepts Systems AG Technoparkstrasse 1 CH-8005 Zuerich T: +41 1 445 29 08 [EMAIL PROTECTED] www.risksys.com
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
