I have the following query :

String q = "select tmarche.lbrm,"
+ " sum(tperfql.mtvtettc), sum(tperfql.mtvtettcm1),"
+ " sum(tperfql.mtmmav), sum(tperfql.mtmmar),"
+ " sum(tperfql.mtvteht)"
+ " from Tmarche as tmarche, Tperfql as tperfql,"
+ " Trespsb as trespsb, Tctscat as tctscat"
+ " where tmarche.cdrm = trespsb.comp_id.cdrm"
+ " and trespsb.comp_id.cdcat = tctscat.comp_id.cdcat"
+ " and tctscat.comp_id.cdscat = tperfql.comp_id.cdscat"
+ " and tperfql.comp_id.cdensg = :idEnseigne"
+ " group by tmarche.lbrm"
+ " order by sum(tperfql.mtvtettc) desc";

I used middlegen to get the mapping and it currently lacks table relationships, which makes the query quite verbose. However, it performs ok up to this point.

Now I need an other sum in my select clause :
+ " sum(tperfql.mtvteht * tobjtcm.txevvtettcp1),"
so I add in the from clause :
+ " Tobjtcm astobjtcm,"
and in the where clause :
+ " and tobjtcm.comp_id.cdscat = tperfql.comp_id.cdscat"
+ " and tobjtcm.comp_id.cdensg = tperfql.comp_id.cdensg"

The addition to the select clause causes an exception :
"HibernateQueryException: undefined alias: tobjtcm"

The underlying database is Oracle 8i (which I am aware is not involved up to this point, but might help to find the proper syntax).

I do not know much of SQL nor HQL so probably this code is quite naive.
Has anyone a working example of a sum of products query involving more than one table (HQL + tables mapping) ?

Thanks.

HQL doesn't support arithmetic expressions in the select clause :(

Hopefully the new query parser slated for 2.2 will help solve this problem.

[quote="Fran

[quote="Fran