How to round a avg

[jill han <jh...@bynum.com>]

I tried to get a query like:

Select id, round(avg(rating), 2)
>From aTable
Where aId = 11111 and rating > 1
Group by(id);

by the codes:

Criteria crit = new Criteria();
crit.add(aTablePeer.a_ID, 11111);
crit.add(aTablePeer.RATING, 1, Criteria.GREATER_THAN);
crit.addSelectColumn(aTablePeer.ID);
crit.addSelectColumn("ROUND(AVG("+aTablePeer.RATING + "), 2)");
crit.addGroupByColumn(aTablePeer.ID);
List result = aTablePeer.doSelect(crit);
Log.debug("query is " + crit.toString());

However, the above codes caused an exception: 
Turbine.handleException: java.lang.Exception:
org.apache.torque.TorqueException: ORA-00933: SQL command not properly
ended

When I checked the debug code, somehow, the codes were interpreted as 
SELECT aTable.ID, Round(AVG(aTable.RATING), 2) FROM aTable, AVG(aTable 
WHERE aTable.RATING>1 AND aTable.a_ID=11111 

How could ", Avg(aTable" append to From clause?

Thanks in advance.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: How to round a avg

[Thomas Fischer <tf...@apache.org>]

Which Torque version are you using ? I would guess this is 3.1.x.

The problem is that Torque needs to get the column name of the database 
column from the sql expression. This was done quite rudimental in Torque 
3.1.x (this is the reason for the mutilated column, Torque thinks that 
AVG(aTable is the column name in the table). I am afraid there is nothing 
you can do against it in 3.1.x. 
This was improved a bit in 3.2-rcX, so your code should work with 
3.2-rcX. (By the way, handling is still not optimal in 3.2-rcX, but the 
necessary changes are beyound 3.2 for compatibility reasons)

However, as I see you are using Turbine, it was discovered recently that 
a change in configuration handling before 3.2-rc1 broke the standard 
configuration of the Torque Security service in Turbine, see the 
discussion in the issue tracker,
http://issues.apache.org/scarab/issues/id/TRQS322
However, this is fixed in subversion. So If you want to use the Torque 
Security Service in Turbine with rc1 or rc2, you either need to tweak the 
configuration a bit or build from svn.

     Thomas


On Mon, 31 Oct 2005, jill han wrote:

> I tried to get a query like:
>
> Select id, round(avg(rating), 2)
>> From aTable
> Where aId = 11111 and rating > 1
> Group by(id);
>
> by the codes:
>
> Criteria crit = new Criteria();
> crit.add(aTablePeer.a_ID, 11111);
> crit.add(aTablePeer.RATING, 1, Criteria.GREATER_THAN);
> crit.addSelectColumn(aTablePeer.ID);
> crit.addSelectColumn("ROUND(AVG("+aTablePeer.RATING + "), 2)");
> crit.addGroupByColumn(aTablePeer.ID);
> List result = aTablePeer.doSelect(crit);
> Log.debug("query is " + crit.toString());
>
> However, the above codes caused an exception:
> Turbine.handleException: java.lang.Exception:
> org.apache.torque.TorqueException: ORA-00933: SQL command not properly
> ended
>
> When I checked the debug code, somehow, the codes were interpreted as
> SELECT aTable.ID, Round(AVG(aTable.RATING), 2) FROM aTable, AVG(aTable
> WHERE aTable.RATING>1 AND aTable.a_ID=11111
>
> How could ", Avg(aTable" append to From clause?
>
> Thanks in advance.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org