Hibernate version:2.1.5
Name and version of the database you are using:SQL Server 2000

Hello,

I am trying to convert the following SQL Server query to HQL:
select datepart(hour, record_date), event_id, count(*) from raw_tb group by datepart(hour, record_date) , event_id

I test the following HQL works with no problem:
Select rawTb.eventId from RawTb rawTb group by {fn HOUR(rawTb.recordDate)}, rawTb.eventId

but fails for the following HQL:
Select {fn HOUR(rawTb.recordDate)}, rawTb.eventId from RawTb rawTb group by {fn HOUR(rawTb.recordDate)}, rawTb.eventId

Can anyone tell me how to correct the HQL? thanks.

Full stack trace of any exception that occurs:

DAOException: net.sf.hibernate.QueryException: undefined alias: {fn [Select {fn HOUR(rawTb.recordDate)}, rawTb.eventId from RawTb rawTb group by {fn HOUR(rawTb.recordDate)}, rawTb.eventId]
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:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1530)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1501)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
.......

have you tried mapping this call to a formula attribute?

First thanks for the help.

I think what you mention is the reason why it fails. I just learn from the forum how to map a database function with one parameter like this:

public class MySQLServerDialect extends SQLServerDialect {
public MySQLServerDialect() {
super();
registerFunction("month", new SQLFunction() {
public Type getReturnType(Type columnType, Mapping mapping) {
return Hibernate.INTEGER;
}
public boolean hasArguments() {
return true;
}
public boolean hasParenthesesIfNoArguments() {
return true;
}
});
}

but I just don't know how to map {fn} and also how to map functions with more than 1 arguement. such as datepart(hour, xx). Since SQL Server only have month, year, day functions but no hour, minute, second function, I don't know what I can do.