>>Query creation problem<<

ivorelvas · **Joined:** Tue Dec 27, 2005 2:55 pm **Posts:** 18

Hello!

I want to make this query in HQL but i get an exception. In mySQL database the query returns the expected result so the sintax is ok... maybe there is some slight difference in HQL. Help would be much appreciated! Here it is:

Code:

String result = (String) session.createQuery("select m.mwoid from Mwo m " + "where timestampdiff(second, m.creationDate, now()) = (select min(timestampdiff(second, m.creationDate, now())) from Mwo m)")
         .uniqueResult();
         return result;

The exception is:

Code:

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode 
 \-[AGGREGATE] AggregateNode: 'min'
    \-[METHOD_CALL] MethodNode: '('
       +-[METHOD_NAME] IdentNode: 'timestampdiff' {originalText=timestampdiff}
       \-[EXPR_LIST] SqlNode: 'exprList'
          +-[IDENT] IdentNode: 'second' {originalText=second}
          +-[DOT] DotNode: 'mwo1_.CreationDate' {propertyName=creationDate,dereferenceType=4,propertyPath=creationDate,path=m.creationDate,tableAlias=mwo1_,className=pt.inescporto.kobas.maintenance.persistenceLayer.Mwo,classAlias=m}
          |  +-[ALIAS_REF] IdentNode: 'mwo1_.MWOID' {alias=m, className=pt.inescporto.kobas.maintenance.persistenceLayer.Mwo, tableAlias=mwo1_}
          |  \-[IDENT] IdentNode: 'creationDate' {originalText=creationDate}
          \-[METHOD_CALL] MethodNode: '('
             +-[METHOD_NAME] IdentNode: 'now' {originalText=now}
             \-[EXPR_LIST] SqlNode: 'exprList'

batmat · **Posted:** Tue Jul 11, 2006 9:04 am

I'm not sure at all, but you should have a look in the archive. I seem to remember a recent thread about a limitation in the AST parser, a.b notation would not be supported in certain parts of the query.

ivorelvas · **Joined:** Tue Dec 27, 2005 2:55 pm **Posts:** 18

well, the thing is that when I execute the query of the select with the MIN function alone in HQL with the function timestampdiff(unit, date1, date2) and with the function datediff(date1, date2), the exception maintains in the first case, while in the second is ok, but I want to get the difference in seconds.
I'm not sure but I think it may be because of the definition in HQL of the parameter SECOND that defines the time unit of the difference between the 2 dates. Any ideas?[/b]

ivorelvas · **Joined:** Tue Dec 27, 2005 2:55 pm **Posts:** 18

How do I represent the time unit "SECOND" in a HQL query? I still get the previous error in this simpler query:

Code:

ArrayList<Double> timediffs = (ArrayList<Double>) 
         session.createQuery("select timestampdiff(SQL_TSI_SECOND, m.creationDate, now()) from Mwo m").list();

STACK:

Code:

ould not get year of last mwo: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'timestampdiff' {originalText=timestampdiff}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[IDENT] IdentNode: 'SQL_TSI_SECOND' {originalText=SQL_TSI_SECOND}
       +-[DOT] DotNode: 'mwo0_.CreationDate' {propertyName=creationDate,dereferenceType=4,propertyPath=creationDate,path=m.creationDate,tableAlias=mwo0_,className=pt.inescporto.kobas.maintenance.persistenceLayer.Mwo,classAlias=m}
       |  +-[ALIAS_REF] IdentNode: 'mwo0_.MWOID' {alias=m, className=pt.inescporto.kobas.maintenance.persistenceLayer.Mwo, tableAlias=mwo0_}
       |  \-[IDENT] IdentNode: 'creationDate' {originalText=creationDate}
       \-[METHOD_CALL] MethodNode: '('
          +-[METHOD_NAME] IdentNode: 'now' {originalText=now}
          \-[EXPR_LIST] SqlNode: 'exprList'