I try execute this select and happend errors.

SELECT:

StringBuffer sb = new StringBuffer("");
sb.append(" SELECT co.descricao, ec.descricao, sum(oc.quantidade * oc.valorUnitario) ");
sb.append(" from ProjetoEspaco as pe, Orcamento as oc, ItemOrcamento as io, CategoriaOrcamento as co, EspacoCultural as ec ");
sb.append(" where pe.projetoCultura = :idProjetoCultural and pe.espacoCultural = ec.codigo ");
sb.append(" and pe.codigo = oc.projetoEspaco and oc.itemOrcamento = io.codigo ");
sb.append(" and io.categoriaOrcamento = co.codigo ");
sb.append(" group by ec.descricao, co.descricao ");
sb.append(" order by ec.descricao asc, co.descricao asc ");
Query query = manager.createQuery(sb.toString());
query.setEntity("idProjetoCultural", new ProjetoCultura(idProjetoCultural));
result = manager.query(query);

ERROR:

COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0611E Invalid column name. SQLSTATE=S0022
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.rsException(SQLExceptionGenerator.java:626)
at COM.ibm.db2.jdbc.app.DB2ResultSet.findColumn(DB2ResultSet.java:2287)
at COM.ibm.db2.jdbc.app.DB2ResultSet.getInt(DB2ResultSet.java:1769)
at com.p6spy.engine.spy.P6ResultSet.getInt(P6ResultSet.java:287)
at net.sf.hibernate.type.IntegerType.get(IntegerType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.hql.QueryTranslator.getResultColumnOrRow(QueryTranslator.java:985)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:222)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1512)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at br.com.bb.qpc.hibernate.ManagerSupport.query(ManagerSupport.java:427)
at br.com.bb.qpc.implementation.SolicitacaoManagerImpl.getResumoOrcamento(SolicitacaoManagerImpl.java:85)
at Start.main(Start.java:24)

In my select if I take off agregate function, instruction run without problem. What can I do?

Turn on sql logging and capture the generated sql (show_sql=true). Compare this query to what you expected.

If the problem is urgent, please consider commercial support.

I alredy do this!
I ran both sql and funcionaly.

Great that you allready looked at the SQL - would you now probably consider telling us what it looks like and what the problem is about it?

Sorry guys, I try explain better.
I suposed agregate function there is a problem.

Because if a use agregation this way there exception:
sum(a * b)

if I use this way not problem:
sum(a)

I saw in others subjects can be resolved this using <property formula/>
but I don

The HQL select parser cannot deal with arithmatic within the select clause (or constants). I suggest select both attibutes separately then use a non-persistent property to get the sum of them.

As you pointed out you can use the formula property to do this as well.

Mapping file would have something like.


Then you would have the non-persistent property in the POJO.

You need to select the correct type for the property for the SQL expression you have included in the formula attribute.

Grrrr - I again did not read correctly again - need to get some sleep. The suggestions are silly. You can do this if you use SQL either directly JDBC or use the SQL query interface. see http://www.hibernate.org/hib_docs/reference/html/query-sql.html

The formula property is a permanent aspect of the domain object so this is used on a load etc. The example is valid (and maybe you learned how to use it ) but its not valid to use the sum aggregration there.

Hope I helped (after all of that).

a) ist should be explicitly noted in the manual that sum(a*b) is not allowed

b) when i do sum(a*b) then the logfile shows me that the statement is correctly executed (even the right value is returned to hibernate); the problem is that hibernate wants to retrieve TWO columns! Is this a bug???