GROUP BY parser error

marco-p · **Posted:** Fri Jan 28, 2005 8:23 am

I'm trying to use the following HQL query:

Code:

select TO_CHAR(order.sentdate, 'MM'), sum( order.ordertotal ) 
from cinetica.ejb.cart.OrderData order 
where TO_CHAR(order.sentdate, 'YYYY') = ? 
group by TO_CHAR(order.sentdate, 'MM')

and I get the error:

Code:

net.sf.hibernate.QueryException: BY expected after GROUP or ORDER: where [select TO_CHAR(order.sentdate, 'MM'), sum( order.ordertotal
) from cinetica.ejb.cart.OrderData order where TO_CHAR(order.sentdate, 'YYYY') = ? group by TO_CHAR(order.sentdate, 'MM')]
        at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:33)
        at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
        at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
        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:294)
        at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1563)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1534)
        at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[snip]

The error message has no sense, so I suppose the problem regards the use of the function.

Hibernate version: Hibernate 2.1, Hibernate 3 beta 2 (using classic package, didn't tested with new antlr)

Full stack trace of any exception that occurs:
see above

Name and version of the database you are using:
Oracle 9.2.0.5

Regards,

Marco

gavin · **Posted:** Fri Jan 28, 2005 8:32 am

Using "order" as an alias is pretty dodgy... its a keyword ...

marco-p · **Posted:** Fri Jan 28, 2005 10:59 am

Ooops, didn't noticed, my fault.
Anyway, I renamed the alias to 'ordine' and I get a different error:

Code:

net.sf.hibernate.QueryException: undefined alias: TO_CHAR [select TO_CHAR(ordine.sentDate, 'MM'), sum( ordine.orderTotal ) from cineti
ca.ejb.cart.OrderData ordine where TO_CHAR(ordine.sentDate, 'YYYY') = ? group by TO_CHAR(ordine.sentDate, 'MM')]
        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:170)
        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:294)
        at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1563)
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1534)
        at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[snip]