sum(qi.volume * qi.avgCost) this is producing error.

and also modifying this to

sum(nvl(qi.volume,0.00) * nvl(qi.avgCost,0.00)) is producing error

and also modifying the same to ;

sum(nvl(qi.volume,Double.valueOf(0.00)) * nvl(qi.avgCost,Double.valueOf(0.00))) is producing error

assume the group by, joins, and others are okay...

Please help whether this is possible in hibernate or not?

Thanks a lot.

In my experience, aggregating something other than a simple column isn't possible in most SQL dialects. I wouldn't really expect it to be available in HQL.

To be sure, see if you you can write a similar SQL query for your database. If so, at a minimum, you could write a native SQL query to handle this. Otherwise, you need to figure out a way to do this without putting the sum() around the multiplication.

I don't know which database you're using, but I just tested it with MySQL 4.1 and the following is a valid expression:


As eagle79 suggested, first check if your database supports this expression and use a native SQL query.
Maybe someone from the Hibernate team can tell you, if such an expression is supported by HQL. I guess it is not.

Best regards
Sven

It is possible (in HB3 only) if the Hibernate dialect has the SQL functions explicitly registered.

Only the latest in high technology: DB2/390 7 ;)

I think I tried a statement that attempted something like this:



but our DB isn't the most modern of systems: doesn't even support CASCADE UPDATE.