Re: GroupBy clause ignored in PersistentBroker.getCount( Query query)

[Danilo Tommasina]

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]


--
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]