version: Hibernate-2.1...

query run though my webapp:

HQL: select con.gender, count(con) from ConsumerBrand cb join cb.brand b join cb.consumer con group by con.gender

SQL: select consumer2_.gender as x0_0_, count(consumer2_.consumer_id) as x1_0_
from consumer_brand consumer0_
inner join brand brand1_ on consumer0_.brand_id=brand1_.brand_id
inner join consumer consumer2_ on consumer0_.consumer_id=consumer2_.consumer_id group by consumer2_.gender

returns:
X: female : 20
X: male : 69
X: Male : 2
X: null : 9

Now, I want to group 'male' and 'Male' together...

so, if I execute the SQL below in psql:

SQL: select lower(consumer2_.gender) as x0_0_, count(consumer2_.consumer_id) as x1_0_
from consumer_brand consumer0_
inner join brand brand1_ on consumer0_.brand_id=brand1_.brand_id
inner join consumer consumer2_ on consumer0_.consumer_id=consumer2_.consumer_id group by lower(consumer2_.gender)

returns:
X: female : 20
X: male : 71
X: null : 9

so now I write the following HQL in my webapp:

select lower(con.gender), count(con) from ConsumerBrand cb join cb.brand b join cb.consumer con group by lower(con.gender)

returns:
net.sf.hibernate.QueryException: undefined alias: lower [select lower(con.gender), count(con) from za.co.vine.consumerManagement.binding.hibernate.ConsumerBrand cb join cb.brand b join cb.consumer con group by lower(con.gender)]
at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:110)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:153)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:140)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:146)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:133)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:352)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:330)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1368)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1332)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1322)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1314)
at za.co.vine.consumerManagement.helpers.ConsumerManagementFactory.getGendersQuery(ConsumerManagementFactory.java:1915)
at za.co.vine.consumerManagement.Manager.getGendersQuery(Manager.java:138)
at za.co.vine.consumerManagement.test.TestManager.<init>(TestManager.java:75)
at za.co.vine.consumerManagement.test.TestManager.main(TestManager.java:139)
za.co.vine.consumerManagement.RecoverableConsumerManagementException: undefined alias: lower [select lower(con.gender), count(con) from za.co.vine.consumerManagement.binding.hibernate.ConsumerBrand cb join cb.brand b join cb.consumer con group by lower(con.gender)]
at za.co.vine.consumerManagement.helpers.ConsumerManagementFactory.getGendersQuery(ConsumerManagementFactory.java:1933)
at za.co.vine.consumerManagement.Manager.getGendersQuery(Manager.java:138)
at za.co.vine.consumerManagement.test.TestManager.<init>(TestManager.java:75)
at za.co.vine.consumerManagement.test.TestManager.main(TestManager.java:139)



Any ideas?

Looks like some limitation in hsql, but you can do it better:

UPDATE consumer_brand set gender = lower(con.gender);

and create a trigger to validate data.

thanks for the help.

Korak