Hello,

I am trying to use decode in hql, if I can get this very simple statement to work, I can get my big query to work.

All I am trying to do is somthing like this to test the hql...

select decode (1, 1, 'one', 'not one') as number
from ResourceImpl

I am using Hibernate tool with eclipse to convert this hql to sql to verify my syntax, it shows an error
---------------------------------------------------------------------------------
java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'decode' {originalText=decode}
\-[EXPR_LIST] SqlNode: 'exprList'
+-[NUM_INT] LiteralNode: '1'
+-[NUM_INT] LiteralNode: '1'
+-[QUOTED_STRING] LiteralNode: ''one''
\-[QUOTED_STRING] LiteralNode: ''not one''
-----------------------------------------------------------------------------------

If I use the decode in where clause, it works fine. I don't know what I am doing wrong. If I write an equivalent sql for the above hql, it works fine.
I spent a lot of time searching the web for examples, etc, but could not find any.

Any help would be greatly appreciated.

Thanks.

Havent tried this, but it looks like you might have to define the "decode" function through your SessionFactory configuration which can then be used in the HQLs. Check out the testcase at
org.hibernate.test.hql.CriteriaHQLAlignmentTest's testClassicHQLAggregationReturnTypes function

I don't know how decode works but this error can surely be resolved by putting double quotes instead of single quote inseide decode function for values.