Hi,

I'm trying to use substring in a hql query.
And I always get an error/exception.
Looking in this forum, I found 2 topics but it stille doesn't work.

I would like to execute the following query :
select distinct substring(cbb.nom, 1, 1) as letter from CyberBase as cbb order by cbb.nom asc

Could you help me to do it ?

All informations needed below

Hibernate version: 2

Mapping documents:

<class name="fr.cdc.cyberbase.bean.structure.CyberBase" table="cyberbase">

<cache usage="read-write"/>

<id name="id">
<generator class="native" />
</id>

<property name="nom" type="string" length="100" not-null="true" />
...
</class>

Code between sessionFactory.openSession() and session.close():
Session session = HibernateUtil.currentSession();

String query = "select distinct substring(cbb.nom, 1, 1) as letter from CyberBase as cbb";
query += getCritereTri("cbb.nom", triCroissant);

List results = session.createQuery(query)
.setCacheable(true)
.list();


Full stack trace of any exception that occurs:
net.sf.hibernate.QueryException: undefined alias: substring [select distinct substring(cbb.nom, 1, 1) as letter from fr.cdc.cyberbase.bean.structure.CyberBase as cbb order by cbb.nom ASC]
at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:103)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:154)
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:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:151)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:140)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:287)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1491)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1462)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:45)
at fr.cdc.cyberbase.bean.acces.CyberBaseDao.getCyberbaseLetters(CyberBaseDao.java:139)

Name and version of the database you are using:
Mysql 4.0.16




Thanx