Java and Hibernate - HQL aggregate alias?

If you look at the SQL that Hibernate has generated, you'll see it has ignored your alias and used its own.

Up vote 2 down vote favorite share g+ share fb share tw.

I am trying to write a simple HQL query with an aggregate function and getting an error. Please help. I have 3 entities Order, OrderItem and Product with the following relationships: Order --> OrderItem (One to many bi-directional) OrderItem --> Product (Many to one Uni-directional) At a high level, a Customer can place an Order that has multiple Order line items with each line item referring to a product and its quantity.

Product is like a master table representing the Product catalog. I am trying to find the total quantity ordered for each product across all Order Items. I am using the following query: select oi.product.

Name, sum(oi. Quantity) as s1 from OrderItem oi group by oi.product. Name order by s1 desc I am aliasing the sum() function with s1 so that I can sort by the same.

But this alias s1 seems to create a problem. Here is the error in the hibernate debug log: 15:39:56,350 DEBUG SQL:111 - select product1_. PRODUCT_NAME as col_0_0_, sum(orderitem0_.

ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_. PRODUCT_ID=product1_. PRODUCT_ID group by product1_.

PRODUCT_NAME order by s1 desc Hibernate: select product1_. PRODUCT_NAME as col_0_0_, sum(orderitem0_. ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.

PRODUCT_ID=product1_. PRODUCT_ID group by product1_. PRODUCT_NAME order by s1 desc 15:39:56,356 DEBUG JDBCExceptionReporter:92 - could not execute query select product1_.

PRODUCT_NAME as col_0_0_, sum(orderitem0_. ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_. PRODUCT_ID=product1_.

PRODUCT_ID group by product1_. PRODUCT_NAME order by s1 desc java.sql. SQLException: Column not found: S1 in statement select product1_.

PRODUCT_NAME as col_0_0_, sum(orderitem0_. ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_. PRODUCT_ID=product1_.

PRODUCT_ID group by product1_. PRODUCT_NAME order by s1 desc at org.hsqldb.jdbc.Util. ThrowError(Unknown Source) at org.hsqldb.jdbc.

JdbcPreparedStatement.(Unknown Source) at org.hsqldb.jdbc.jdbcConnection. PrepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher. GetPreparedStatement(AbstractBatcher.

Java:534) at org.hibernate.jdbc.AbstractBatcher. GetPreparedStatement(AbstractBatcher. Java:452) at org.hibernate.jdbc.AbstractBatcher.

PrepareQueryStatement(AbstractBatcher. Java:161) at org.hibernate.loader.Loader. PrepareQueryStatement(Loader.

Java:1577) at org.hibernate.loader.Loader. DoQuery(Loader. Java:696) at org.hibernate.loader.Loader.

DoQueryAndInitializeNonLazyCollections(Loader. Java:259) at org.hibernate.loader.Loader. DoList(Loader.

Java:2232) at org.hibernate.loader.Loader. ListIgnoreQueryCache(Loader. Java:2129) at org.hibernate.loader.Loader.

List(Loader. Java:2124) at org.hibernate.loader.hql.QueryLoader. List(QueryLoader.

Java:401) at org.hibernate.hql.ast. QueryTranslatorImpl. List(QueryTranslatorImpl.

Java:363) at org.hibernate.engine.query.HQLQueryPlan. PerformList(HQLQueryPlan. Java:196) at org.hibernate.impl.SessionImpl.

List(SessionImpl. Java:1149) at org.hibernate.impl.QueryImpl. List(QueryImpl.

Java:102) at com.bpp.cm. ProductRepository. GetTopFiveProducts(ProductRepository.

Java:136) at com.bpp.cm.Main. TestGetTop5Products(Main. Java:315) at com.bpp.cm.Main.

TestProduct(Main. Java:233) at com.bpp.cm.Main. Main(Main.

Java:40) 15:39:56,377 WARN JDBCExceptionReporter:100 - SQL Error: -28, SQLState: S0022 15:39:56,378 ERROR JDBCExceptionReporter:101 - Column not found: S1 in statement select product1_. PRODUCT_NAME as col_0_0_, sum(orderitem0_. ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.

PRODUCT_ID=product1_. PRODUCT_ID group by product1_. PRODUCT_NAME order by s1 desc 15:39:56,379 DEBUG JDBCTransaction:186 - rollback 15:39:56,379 DEBUG JDBCTransaction:197 - rolled back JDBC Connection 15:39:56,379 DEBUG ConnectionManager:427 - aggressively releasing JDBC connection 15:39:56,380 DEBUG ConnectionManager:464 - releasing JDBC connection (open PreparedStatements: 1, globally: 1) (open ResultSets: 0, globally: 0) org.hibernate.exception.

SQLGrammarException: could not execute query at org.hibernate.exception. SQLStateConverter. Convert(SQLStateConverter.

Java:90) at org.hibernate.exception. JDBCExceptionHelper. Convert(JDBCExceptionHelper.

Java:66) at org.hibernate.loader.Loader. DoList(Loader. Java:2235) at org.hibernate.loader.Loader.

ListIgnoreQueryCache(Loader. Java:2129) at org.hibernate.loader.Loader. List(Loader.

Java:2124) at org.hibernate.loader.hql.QueryLoader. List(QueryLoader. Java:401) at org.hibernate.hql.ast.

QueryTranslatorImpl. List(QueryTranslatorImpl. Java:363) at org.hibernate.engine.query.HQLQueryPlan.

PerformList(HQLQueryPlan. Java:196) at org.hibernate.impl.SessionImpl. List(SessionImpl.

Java:1149) at org.hibernate.impl.QueryImpl. List(QueryImpl. Java:102) at com.bpp.cm.

ProductRepository. GetTopFiveProducts(ProductRepository. Java:136) at com.bpp.cm.Main.

TestGetTop5Products(Main. Java:315) at com.bpp.cm.Main. TestProduct(Main.

Java:233) at com.bpp.cm.Main. Main(Main. Java:40) Caused by: java.sql.

SQLException: Column not found: S1 in statement select product1_. PRODUCT_NAME as col_0_0_, sum(orderitem0_. ORDER_ITEM_QUANTITY) as col_1_0_ from ORDER_ITEM orderitem0_, PRODUCT product1_ where orderitem0_.

PRODUCT_ID=product1_. PRODUCT_ID group by product1_. PRODUCT_NAME order by s1 desc at org.hsqldb.jdbc.Util.

ThrowError(Unknown Source) at org.hsqldb.jdbc. JdbcPreparedStatement.(Unknown Source) at org.hsqldb.jdbc.jdbcConnection. PrepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.

GetPreparedStatement(AbstractBatcher. Java:534) at org.hibernate.jdbc.AbstractBatcher. GetPreparedStatement(AbstractBatcher.

Java:452) at org.hibernate.jdbc.AbstractBatcher. PrepareQueryStatement(AbstractBatcher. Java:161) at org.hibernate.loader.Loader.

PrepareQueryStatement(Loader. Java:1577) at org.hibernate.loader.Loader. DoQuery(Loader.

Java:696) at org.hibernate.loader.Loader. DoQueryAndInitializeNonLazyCollections(Loader. Java:259) at org.hibernate.loader.Loader.

DoList(Loader. Java:2232) ... 11 more hibernate link|improve this question edited Jun 29 '10 at 14:04Péter Török53.1k865136 asked Jun 29 '10 at 13:59bjagan111.

If you look at the SQL that Hibernate has generated, you'll see it has ignored your alias and used its own. Instead, simply group by the same aggregate you're using in the select select oi.product. Name, sum(oi.

Quantity) from OrderItem oi group by oi.product. Name order by sum(oi. Quantity) desc.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.