Re: Report Queries

[Jakob Braeuchi]

hi luis,

could you please provide the generated sql ?

jakob

Luis Tiago wrote:

Hi,
I'm having problems doing report queries.
The results are not what I expect. I do not know what I'm doing wrong!
Exemple: 
I have these records in database, table movements

MOVEMENTID | USERID | COPYID | SIRIUSID 
2.361                  3.682      1.350         8 
2.362                  3.682      1.351         11 
2.381                  3.682      1.351         11 
2.421                  4.402      1.370         958447 
2.441                  4.402      1.370         958447 

I want to execute the follow SQL statement:
        select siriusid, count(*) from movements group by siriusid order by count(*) 
desc
wich produces the follow results

SIRIUSID           |         EXPRESSION1 
11                                          2 
958447                                   2 
8                                            1



Ok! now using reportQueries like the OJB tutorial says:

.....

    Criteria criteria=new Criteria()

    ReportQueryByCriteria rq = QueryFactory.newReportQuery(Movements.class, criteria);

    rq.setColumns(new String[] { "siriusId", "count(siriusId)" });

    rq.addGroupBy("siriusId");

    rq.addOrderByDescending("count(siriusId)");

    Iterator it = broker.getReportQueryIteratorByQuery(rq);

    while (it.hasNext()) {

        Object[] data = (Object[]) it.next();

        String siriusId = new String("" + data[0]);

        Integer total = new Integer("" + data[1]);

        top.put(siriusId, total);  //hastable

    }

....

wich produces this result:

siriusId  |  count(siriusId)

11         |     2

8           |     1

958447  |     2

this result is not in order, it seems that this statement is not working  < rq.addOrderByDescending("count(siriusId)") >

And if I change the collums order < rq.setColumns(new String[] { "count(siriusId)", "siriusId" });>

the results are: 

siriusId  | count(siriusId)

958447  |     2

8           |    1

Strange??? What I'm doing wrong???

If anyone could help me I would be thankfull.

Where can I find more complex tutorials and examples using ReportQueries!



THANKS

        Tiago Rico


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]